+ Reply to Thread
Results 1 to 12 of 12

Inserting multiple excel files into multiple word documents

  1. #1
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Inserting multiple excel files into multiple word documents

    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

  2. #2
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    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]

  3. #3
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Inserting multiple excel files into multiple word documents

    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.

  4. #4
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    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).

  5. #5
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Inserting multiple excel files into multiple word documents

    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.

  6. #6
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    Hi Laura,

    Give the following a try:
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Inserting multiple excel files into multiple word documents

    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?

  8. #8
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    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.

  9. #9
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Inserting multiple excel files into multiple word documents

    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

  10. #10
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    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'.

  11. #11
    Registered User
    Join Date
    09-04-2012
    Location
    Stevenage, UK
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Inserting multiple excel files into multiple word documents

    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

  12. #12
    Forum Guru macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,495

    Re: Inserting multiple excel files into multiple word documents

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1