Import from Excel

[pdberger]

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.

 

[Peter Jamieson]

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=wdNotAMergeDocument

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]

Peter --

Thanks for such a complete answer. I'll figure out the best strategy from
your kind information.

Peter