I have a Word document with an OLE object linked to a spreadsheet.

I'd like to make this Word document a template and have my table auto-populate by referencing a spreadsheet in the "current directory" with a specific name (call it "test.xls" for now). "test.xls" would be located in many other directories, but each "test.xls" spreadsheet would be unique with different data - corresponding to the project. My goal is to copy the word template file into any one of those directories with the "test.xls" spreadsheet and have my document auto populate with the spreadsheet in the same directory.

Sounds simple, but word won't let me define this in the OLE options. It appears that the source file path must be defined to a specific (static) directory and can't just reference the current active directory.

Anyone have a work around? VBA perhaps?

Thanks,
Shaun

While having multiple templates in multiple folders is generally not a great idea, in this case you could:

have the template, when it is used to create a new file, use DocumentNew to pull in your spreadsheet from the current folder. To get the current folder (i.e. the folder the template dotm file is in) use:

AttachedTemplate.Path & "\"

therefore to point to a xls file in the same folder:
AttachedTemplate.Path & "\test.xls"

Shaun,

What you're describing would require relative file paths, which Word's LINK field (used for linking to Excel) doesn't support. However, you can achieve such an outcome with a macro. To see how to do so, check out the macro attached to my post at:
http://windowssecrets.com/forums/sho...External-Files

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hard to say if macropod solution is what you need. You do not state the actual steps involved. If the template start with NO spreadsheet, then my suggestion may work for you. Record how to bring in the spreadsheet, and add that to your DocumentNew() of the template. Use the path string I posted in the code to bring in the spreadsheet.

You can now copy the template to any folder. When you use it to create a new document, it will bring in the spreadsheet from the folder that specific template file is in.

IMPORTANT! You should add some error trapping to make sure that things are handled well if the spreadsheet is NOT present.

Hi Paul:
Exactly! I read over the link you provided, and tried a couple of attempts, but I'm not getting the right result. For example, I currently have the following link...
( LINK Excel.Sheet.12 "\\\\MINESTEEL\\JOBS\\MFL Jobs\\6000\\6008 - NMT (PTFI 22 ea 20m3 Cars)\\Manual\\Working\\Project-Manual-Variables.xlsx" Sheet1!Product.Name \a \t )

I modified it as follows, but Word auto corrects it back the original format above.
( LINK Excel.Sheet.12 "\\\\MINESTEEL\\JOBS\\MFL Jobs\\6000\\6008 - NMT (PTFI 22 ea 20m3 Cars)\\Manual\\Working\\Project-Manual-Variables.xlsx\\..\\Project-Manual-Variables.xlsx" Sheet1!Product.Name \a \t )

I also tried this way below, which would be ideal, but it comes back with errors. Any idea's?
( LINK Excel.Sheet.12 "\\..\\Project-Manual-Variables.xlsx" Sheet1!Product.Name \a \t )

Thanks,
Shaun

Please re-read the advice given, especially the part about the macro...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi Paul:

I've reread your thread several time, enabled macros and tried the macro you linked to, but it seems to cause Word and/or Excel to hang on my system. I watch my task manager and the memory just continues to increase over time.

I'm attaching a very bare bone example of my work so far (both the Word template and the Excel File). The word document has your macro embedded but I just can't get the Word document to update the link paths.

Can you let me know what I'm doing wrong?

Thanks,
Shaun

Attached Files

template.zip (54.2 KB, 12 views)

I downloaded your zip file, extracted the contents an opened the document; the links updated in less than 1 second. Unless your macro security is too high you should see the same behaviour.

Note: you shouldn't necessarily expect to see any differences in the document unless you look at the field codes.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Really??? Well that's promising. I just ran the document on my colleagues machine (since he has a different version of Office), but he experienced the same thing as me - the script starts, but doesn't appear to end. We notice the first field goes blank but never repopulates with the new data - and all the other fields remain unchanged. There's only one security setting in Word that I'm aware of that enables Macro's (in the Trust Center Dialog). Are there any other security options that I should check for?

Thanks,
Shaun

-=UPDATE=-

I decided to copy & paste the files from my Network Directory directly onto my Desktop and the script "Sort Of" Worked. Everything updated within the body, but not the header/footer.

I Wonder why the network location behaved differently. Any idea's?

The macro should work across networks, though I am aware of reports that LINK fields sometimes get corrupted in such situations, with the filepath & name getting joined to the sheet address, with the result that the LINK field no longer works as intended. Since this happens independently of the macro, it suggests some form of corruption is occurring.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Hi macropod,

Many thanks for your very useful macro (http://windowssecrets.com/forums/sho...External-Files) !

I still have a problem using it with Office 2010. Actually, When it replaces the path it removes the formatting. Is there a way to keep this formatted, it will be really great ?

As complementary information, I use LINK content.
Many thanks for your reply.

Changing the link path doesn't of itself have any impact on formatting. If that is changing, it would be liable to do so even if the path wasn't changed - simply updating the link would do that - in which case there's a different issue to be addressed.

If the reformatting entails unwanted resizing of the linked Excel objects, this is a known issue with Word 2010 and there is registry key to fix it:
Open the Registry Editor, then navigate to:
[HKEY_CURRENT_USER\Software\Microsoft\Office\14.0\E xcel\Options]
Add a new DWORD value:
QFE_Boston
Set the new DWORD value to 1

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks macropod.

Actually, I really think the replace script breaks formatting and I explain you why :

• When the path is changed and the document is closed. If I re-open it again, even when changing data from Excel linked file, data are refreshing without changing formatting.
• By the way, I can observe that the \a option disappear when path is changed. I don't know why this option is for but I thought that could be the flag. What do you think ? I am not sure at all about that but I'm curious and may be a clue.

To finish, I've read in others posts/forums what you wrote on relative path, and even try to use {FILENAME \p} with LINK in vain ... can you confirm that there is no way to make LINK work with that workaround ?


Thanks for your reply.

Let's just say the macro has been used by thousands of others who have never had occasion to complain about the format changing.
the \a switch is for automatic updates. Since the macro is doing the updates, that switch becomes inappropriate and is liable to double the number of updates required.
I think you've answered that for yourself...

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

That's clear enough ! This means it should be possible

Indeed. I'll remove it.

I'll investigate again on my part. Thanks for quick and accurate replies.