Hi,
I have 108 separate, named, excel files that I need to insert into 108 separate, named, word documents. The file name for the excel document will be referred to in the word document. Is there a way to use a macro or similar to achieve this automatically? If attachments would be useful, just let me know. Thanks
Laura
Do the documents and Excel files share the same names? Are they in the same folder? If not, how does one locate the filenames in the documents and the paths? Where in the documents are the Excel files to be inserted? Are they to be linked or embedded?
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
The plan wasn't for them to share the same filename but if it makes things easier they can do. They can be in the same folder or a separate one, it doesn't really matter to me. My original option was to have the excel filename as a reference within the document, but as I've said, they can share a filename if that makes things easier. I would like the excel files to be inserted into the last page of the document, on their own, and they just need to be viewed rather than edited, so preferably embedded, but could link to a file as well.
Hi Laura,
It may not matter to you, what filenames or folders are used, but it makes a great deal of difference as to how a macro might be coded. If the documents & workbooks don't have the same name, then there needs to be a precise way of identifying the filename in the document's content. Similarly, if they're not in the same folder, one needs an equally precise way of identifying that folder (eg as part of the string that includes the filename).
As for linking-vs-embedding, the issue isn't about editing from within the document (for which embedding makes more sense) so much as it is about whether you want to be able to edit the workbook and have the changes there reflected in the document (and even that relies on the path to the Excel workbook remaining unchanged).
Apologies, maybe I wasn't clear. I am aware that the location and names of the files will affect the macro, what I was saying was that I could be flexible about both of these things if it made the macro easier to write/run. Let's assume that they are in the same folder and they have the same file name as this appears to be the easiest way to set them up.
In terms of embedding, the excel file will not be amended after it has been inserted.
Thanks for your help so far.
Hi Laura,
Give the following a try:
Please Login or Register to view this content.
Hi, I did a test with some dummy documents and spreadsheets and got an error saying that 'Word cannot obtain the data for the [file location] link.'
Also, is this a macro that I will need to run on each document separately or is there a way of applying it to all documents within the folder?
Ah, it's the little things that get you! Change "xls" to ".xls"
And yes, the macro should process all documents in the selected folder.
Hmm, that's still not working. When I debug the code it's highlighting the following area (no idea if that actually means anything!):
.Shapes.AddOLEObject Anchor:=Rng, ClassType:="Excel.Sheet", _
FileName:=strFolder & "\" & Split(strFile, ".")(0) & ".xls", _
LinkToFile:=False, DisplayAsIcon:=False
Just to check I'm doing things right (I'm still very much a beginner with all of this) for this test I have 3 macro-enabled word documents and 3 excel files, called Test1, Test2 and Test3 respectively. I'm inserting the macro into the word document Test1. Does this all sound ok?
Thanks for your patience
Hi Laura,
The macro should not be in any of the documents it is to process. That's because it opens, modifies, then saves and closes those documents. If it closes its own document, it will stop running. Also, though it shouldn;t make any difference, there is no need for the target documents to be macro-enabled. However, that suggests to me that you might be working with xlsx or xlsm files; if so, add the 's' or 'm' to the '.xls'.
Huzzah, changed the file extension to .xlxs and it's worked :D
I just have 1 final request, is it possible to specify the page of the target document that it is inserted into?
Thanks so much for your help
Hi Laura,
Yes, that's possible. The code presently looks for the end of the document because that's what you asked for. If you want to specify a page, change:
Set Rng = .Range.Characters.Last
to:
Set Rng = .GoTo(What:=wdGoToPage, Name:="4")
where '4' is the page number.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks