Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro or drop down list ?
I have a fairly complex mail merge master that extracts data from an Excell
workbook-That works fine! With 50+ sheets in the workbook, I need to edit the MM Master with 4 key items of information prior to completing the mailmerge of each sheet. I have these 50+ X 4 items of information stored on a separate worksheet. What is the best method of inserting these four items of key information into my MMM to save much time, and more importantly avoid error when typing these in manually? My knowledge of both the above options is NIL- So please keep it as simple as possible ? Many Thanks |
#2
|
|||
|
|||
Hi ?B?R3JhaGFt?=,
I have a fairly complex mail merge master that extracts data from an Excell workbook-That works fine! With 50+ sheets in the workbook, I need to edit the MM Master with 4 key items of information prior to completing the mailmerge of each sheet. I have these 50+ X 4 items of information stored on a separate worksheet. What is the best method of inserting these four items of key information into my MMM to save much time, and more importantly avoid error when typing these in manually? My knowledge of both the above options is NIL- So please keep it as simple as possible ? Based on what you tell us, I'm thinking a LINK field could be what you need. Copy each of the four sets (cells?) of information for one sheet, go to Edit/Paste Special in Word; activate "link" and choose "unformatted text". If that looks pretty much like what you need, press Alt+F9 to look at the LINK field code. You should see it references the sheetname!r1c1 cell. In Excel, assign a range name to each cell that clearly identifies what it is (datasheet1info1, for example). Now substitute the range names for the cell references in the LINK fields. When you merge to a different sheet, all you need to do is change the 1 to a 2 for datasheet (for example) for the four Link fields. This can be done quickly using Find/Replace. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#3
|
|||
|
|||
Cindy, Many thanks for your prompt reply. I'll try to follow your
instructions and let you know how I get on!! "Cindy M -WordMVP-" wrote: Hi ?B?R3JhaGFt?=, I have a fairly complex mail merge master that extracts data from an Excell workbook-That works fine! With 50+ sheets in the workbook, I need to edit the MM Master with 4 key items of information prior to completing the mailmerge of each sheet. I have these 50+ X 4 items of information stored on a separate worksheet. What is the best method of inserting these four items of key information into my MMM to save much time, and more importantly avoid error when typing these in manually? My knowledge of both the above options is NIL- So please keep it as simple as possible ? Based on what you tell us, I'm thinking a LINK field could be what you need. Copy each of the four sets (cells?) of information for one sheet, go to Edit/Paste Special in Word; activate "link" and choose "unformatted text". If that looks pretty much like what you need, press Alt+F9 to look at the LINK field code. You should see it references the sheetname!r1c1 cell. In Excel, assign a range name to each cell that clearly identifies what it is (datasheet1info1, for example). Now substitute the range names for the cell references in the LINK fields. When you merge to a different sheet, all you need to do is change the 1 to a 2 for datasheet (for example) for the four Link fields. This can be done quickly using Find/Replace. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#4
|
|||
|
|||
Fabulous! Works a treat - though pressing Alt -F9 was a bit scary!!!
I didn't get the bit about range names-but I'm happy with it as it is. By keeping the source file open, I can quickly check which Row to change to for the Find/Replace option. Can I get the Links to auto-update once I've changed them, or does this stil have to be done manually? Once again many thanks - You've made a tedious job much quicker and avoided the problem of typos. "Graham" wrote: Cindy, Many thanks for your prompt reply. I'll try to follow your instructions and let you know how I get on!! "Cindy M -WordMVP-" wrote: Hi ?B?R3JhaGFt?=, I have a fairly complex mail merge master that extracts data from an Excell workbook-That works fine! With 50+ sheets in the workbook, I need to edit the MM Master with 4 key items of information prior to completing the mailmerge of each sheet. I have these 50+ X 4 items of information stored on a separate worksheet. What is the best method of inserting these four items of key information into my MMM to save much time, and more importantly avoid error when typing these in manually? My knowledge of both the above options is NIL- So please keep it as simple as possible ? Based on what you tell us, I'm thinking a LINK field could be what you need. Copy each of the four sets (cells?) of information for one sheet, go to Edit/Paste Special in Word; activate "link" and choose "unformatted text". If that looks pretty much like what you need, press Alt+F9 to look at the LINK field code. You should see it references the sheetname!r1c1 cell. In Excel, assign a range name to each cell that clearly identifies what it is (datasheet1info1, for example). Now substitute the range names for the cell references in the LINK fields. When you merge to a different sheet, all you need to do is change the 1 to a 2 for datasheet (for example) for the four Link fields. This can be done quickly using Find/Replace. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#5
|
|||
|
|||
Hi ?B?R3JhaGFt?=,
pressing Alt -F9 was a bit scary!!! Yes, the first couple of times it is a shock to the system. I probably should include a warning about "don't try if you're faint of heart" g Can I get the Links to auto-update once I've changed them, or does this stil have to be done manually? Ctrl+A (select all) then F9 (update fields) is probably the best way to go. The automatic update really only works when the application you've linked in sends a message that something has changed. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to extend drop down list on a form to over 50 charecters? | Microsoft Word Help | |||
Drop Down List | Microsoft Word Help | |||
Save As is not working due to macro security settings | Microsoft Word Help | |||
2000 to 2002 macro and "Could not open macro storage" | Mailmerge | |||
drop down list | Tables |