Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Import from Excel
Greetings --
I am setting up a Word document to import A LOT of information from an Excel spreadsheet. Basically, the spreadsheet captures a couple hundred pieces of info and translates them into text. Each datapoint ranges from a couple of words to a couple of sentences. The Word document serves as a place to sequence and format those sentences into a long report. I intend to always keep them together in the same folder. My problem: As I set up the Word document to import info from Excel, I want to do it in a way so that, if I move the two files to another folder, the Word looks to the new folder it's in, rather than the original folder in which the Excel spreadsheet resides now. I don't know whether this is a OLE problem, if it's something I'll just have to find-and-replace when I move it, or what. Thanks in advance. |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Import from Excel
It depends partly on how you are inserting the information in the Word
document. It sounds as if you are probably using OLE LINK fields to do it, in which case even though the LINK field in Word contains the full path name of the Excel file, when you move both files to a new folder and open the .doc, if Word Tools|Options|General|"Update automatic links at Open" is checked, Word should offer to update the links, and in this case it should also update the path, even if you leave a copy of the Excel worksheet at the old location. If "Update automatic links at Open" is not checked, nothing happens when you open the .doc. If you select all the LINK fields and click Edit|Update Link, the path will /not/ be updated. If however, you go into Edit|Links... select all the Links and click "Update Now", Word will change the path to point to the new location. At least that's how it works here with a simple set of tests using Win XP Pro SP2 and Office 2003. However, other settings may also have a part to play - e.g. Word Tools|Options|General|Web options|Files|"Update links on save" (which may be actioned even thought your document isn't a .htm file or on a web). If you are using Mailmerge to do it and you move the two files to a new folder, all that will happen is that Word will probably not find the data source when it next opens the .doc, and you will be prompted to locate it. (However, I would also avoid putting your document and Excel file deep in a folder hierarchy, because there is a limitation on Word's connection string length that means it constantly loses the data source.) You can't really automate that reconnection unless you ensure that either a. the data source is diconnected from the Word document before you move the two files or b. you leave a copy of the Excel file in the old location. The problem is that Word tries to connect tot eh data source /before/ any macro, even AutoOpen macros, are allowed to run. To disconnect using VBA, all you need is ActiveDocument.MailMerge.MainDocumentType=wdNotAMe rgeDocument Programmatic reconnection may be a matter of macro recording the original Mail Merge connection action and modifying the code so it looks at the pathname of the Active document. Peter Jamieson "pdberger" wrote in message news Greetings -- I am setting up a Word document to import A LOT of information from an Excel spreadsheet. Basically, the spreadsheet captures a couple hundred pieces of info and translates them into text. Each datapoint ranges from a couple of words to a couple of sentences. The Word document serves as a place to sequence and format those sentences into a long report. I intend to always keep them together in the same folder. My problem: As I set up the Word document to import info from Excel, I want to do it in a way so that, if I move the two files to another folder, the Word looks to the new folder it's in, rather than the original folder in which the Excel spreadsheet resides now. I don't know whether this is a OLE problem, if it's something I'll just have to find-and-replace when I move it, or what. Thanks in advance. |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
Import from Excel
Peter --
Thanks for such a complete answer. I'll figure out the best strategy from your kind information. Peter "Peter Jamieson" wrote: It depends partly on how you are inserting the information in the Word document. It sounds as if you are probably using OLE LINK fields to do it, in which case even though the LINK field in Word contains the full path name of the Excel file, when you move both files to a new folder and open the .doc, if Word Tools|Options|General|"Update automatic links at Open" is checked, Word should offer to update the links, and in this case it should also update the path, even if you leave a copy of the Excel worksheet at the old location. If "Update automatic links at Open" is not checked, nothing happens when you open the .doc. If you select all the LINK fields and click Edit|Update Link, the path will /not/ be updated. If however, you go into Edit|Links... select all the Links and click "Update Now", Word will change the path to point to the new location. At least that's how it works here with a simple set of tests using Win XP Pro SP2 and Office 2003. However, other settings may also have a part to play - e.g. Word Tools|Options|General|Web options|Files|"Update links on save" (which may be actioned even thought your document isn't a .htm file or on a web). If you are using Mailmerge to do it and you move the two files to a new folder, all that will happen is that Word will probably not find the data source when it next opens the .doc, and you will be prompted to locate it. (However, I would also avoid putting your document and Excel file deep in a folder hierarchy, because there is a limitation on Word's connection string length that means it constantly loses the data source.) You can't really automate that reconnection unless you ensure that either a. the data source is diconnected from the Word document before you move the two files or b. you leave a copy of the Excel file in the old location. The problem is that Word tries to connect tot eh data source /before/ any macro, even AutoOpen macros, are allowed to run. To disconnect using VBA, all you need is ActiveDocument.MailMerge.MainDocumentType=wdNotAMe rgeDocument Programmatic reconnection may be a matter of macro recording the original Mail Merge connection action and modifying the code so it looks at the pathname of the Active document. Peter Jamieson "pdberger" wrote in message news Greetings -- I am setting up a Word document to import A LOT of information from an Excel spreadsheet. Basically, the spreadsheet captures a couple hundred pieces of info and translates them into text. Each datapoint ranges from a couple of words to a couple of sentences. The Word document serves as a place to sequence and format those sentences into a long report. I intend to always keep them together in the same folder. My problem: As I set up the Word document to import info from Excel, I want to do it in a way so that, if I move the two files to another folder, the Word looks to the new folder it's in, rather than the original folder in which the Excel spreadsheet resides now. I don't know whether this is a OLE problem, if it's something I'll just have to find-and-replace when I move it, or what. Thanks in advance. |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
import transactions from excel | Page Layout | |||
Address Book - Import from Excel | New Users | |||
import from excel | Tables | |||
Import Excel sheet | Page Layout | |||
Can I Import Graphs from Excel? | Microsoft Word Help |