+ Reply to Thread
Results 1 to 13 of 13

Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    Farmington Hills, MI
    MS-Off Ver
    Office 2010
    Posts
    5

    Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Whenever the following code snippet runs, I get a "Type Mismatch" error when the penultimate line tries to execute.

    copy to clipboard
    Please Login or Register  to view this content.
    The code is being run in an Excel 2010 VBA module, and the subroutine, from which this snippet comes, opens up an instance of Word, populates a new document with text, but then must insert a table that is in the current workbook from which the code is being executed. I have declared my Excel range as a variant, which the Insertfile method seems to require. On a related note, how do I de-select the range? The wdCollapseEnd does not do it, although it does move the insertion point.

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,158

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Hi jtwade,

    Your reference to ActiveDocument is unqualified, so Excel won't know you mean a Word document. You don't need to select the range, so you can do away with myRange.Select. That also takes care of de-selecting afterwards.
    Cheers,
    Paul Edstein
    [MS MVP - Word]

  3. #3
    Registered User
    Join Date
    04-16-2012
    Location
    Farmington Hills, MI
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Thanks for your reply. Unfortunately, I'm either not understanding what you mean or your suggestion is not working. based on my understanding of your reply, I made the following changes to the code snippet:
    copy to clipboard
    Please Login or Register  to view this content.
    Now because the "myRange.Select" line is commented out, the insertion point doesn't move to the end of the paragraph where I need it to be. And I'm still getting the "Type Mismatch" error on the second-to-last line.

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,158

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Hi jtwade,

    There is nothing in the code you've posted to show whether, or how, you're actually initializing a Word session. Simply adding a 'Word.' prefix to a line here or there isn't enough. See, for example: http://www.excelforum.com/showthread...=1#post2762442

  5. #5
    Registered User
    Join Date
    04-16-2012
    Location
    Farmington Hills, MI
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Yup, I have all the necessary declarations. Here is the first part of my program:

    copy to clipboard
    Please Login or Register  to view this content.
    I'm doing things a little bit differently than your code example (e.g., I'm "creating" the object rather than specifying "New"), although I tried your example and I like it better because it seems to solve the problem of getting the new document to be the "active" document. That's why I close and then re-open the file. However, I'm still getting the "Type Mismatch" error when it gets to the second to last line in the first code snippet I posted. Everything else is working exactly as
    intended up to that point; i.e., my Word document is being created, is being populated with information from the Excel workbook, etc. until it reaches the point where I need to insert a range from Excel. And the Microsoft Help for the InsertFile method is useless because it doesn't give any information on the syntax to use, or any meaningful examples.

  6. #6
    Valued Forum Contributor
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,158

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    As indicated previously, you aren't referencing your Word objects properly. For example, you should be using:
    Set tempDocument = myWord.Documents.Add
    and
    myWord.Documents.Open (tempFullPathName)
    etc.

  7. #7
    Registered User
    Join Date
    04-16-2012
    Location
    Farmington Hills, MI
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Thanks again for your responses, although I'm still getting the "Type Mismatch" error. This time, I have attached the entire VBA code module here. I'm pretty sure everything is declared properly, as you suggested, but when I execute the program, everything works exactly as intended up until it tries to insert the Excel range from the workbook into the Word document. I tried to upload the entire workbook but it was too large. I hope the code module helps, though.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,287

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Hi @jtwade,

    You can zip the file before uploading. That should allow you to upload the file.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  9. #9
    Valued Forum Contributor
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,158

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Hi ,

    Try the following re-worked version of your 'generate_SE_AFN' sub. Apart from being more efficient, I've implemented a copy/paste of your 'FTAP_Table' range (which, I note wasn't properly declared or set). For whatever reason, in my testing the Insertfile method seems to want to render the binary input data as a textstream and I don't have time to get to the bottom of the issue. You may want to change the paste format.
    copy to clipboard
    Please Login or Register  to view this content.

  10. #10
    Valued Forum Contributor
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    1,158

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Cross-posted at: http://answers.microsoft.com/en-us/o...c-62da3ee52cd9
    Please read RULE 8 of this forum's rules:
    Don't cross-post without a link. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post. Expect cross-posts without a link to be closed.

  11. #11
    Registered User
    Join Date
    04-16-2012
    Location
    Farmington Hills, MI
    MS-Off Ver
    Office 2010
    Posts
    5

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Thanks for your response. After I played around with the InsertFile method a little more, I realized that it does not work with Excel 2010 files - probably has something to do with the fact that they are really zipped archives disguised as a single file. And I also received binaryinput data when I tried to import a range from an Excel 97-2003 formatted spreadsheet. Yet when I used InsertFile to import text from an older Word document (before Word 2010), the InsertFile method seemed to work just fine.

    By the way, the fact that Excel 2010 is a zipped archive also explains why zipping it up to post to this forum wouldn't work - the workbook is already compressed.

    Regarding the declaration of the range "FTAP", I tried everything here, but according to the technical documentation, the InsertFile method takes a Variant data type for the optional "Range" argument - that's why I didn't declare FTAP as an excel "Range" in the version I posted (but I had tried that in previous versions of my application).

    Anyway, I like the programming changes you made - my application is definitely in better shape than before. Thanks again for all your help!

  12. #12
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,287

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    FYI... Post-2003 office files can still lose some size when compressed even though they are compressed file types. Just created a dummy 7.21 MB file and compressed into ZIP and RAR file types. The results of compression were 6.28 MB (ZIP) and 6.20 MB (RAR).

    Saving the same file as a Binary file type (.xlsb) reduced the file size to 5.39 MB before additional compression; 4.69 MB after compression.

    So as you can see, there is more than one way to put your files on diet

    abousetta

  13. #13
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    4,287

    Re: Inserting an Excel Range Into A Word Document, via Insertfile method, using Excel VBA

    Even more fascinating, when I saved the same file as pre-2007 version (.xls), the file bloated to 11.7 MB (expected) but when I compressed it... shrunk to a mere 3.52 MB

+ 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