+ Reply to Thread
Results 1 to 17 of 17

Macro to Import Multiple Word Tables Into Excel Worksheet

  1. #1
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Macro to Import Multiple Word Tables Into Excel Worksheet

    As the title says I am trying to take multiple tables from a Word document and import them into an Excel worksheet. Currently I have found two versions that when combined, could yield what I am looking for. The first one imports the table's data from Word, but does not maintain formatting of the table (font, colors, rows/columns etc.):
    Please Login or Register  to view this content.
    The next code maintains formatting, but only imports/pastes one table:
    Please Login or Register  to view this content.
    For the second one, I do not like the fact that it is calling a specific Workbook to paste into. If I could somehow maintain the ability to import/past multiple tables while keeping formatting that would be perfect. An extra bonus would be to import each table within the Word document into individual Worksheets in Excel. I am also using Office 2010. Thanks! References: http://www.mrexcel.com/forum/excel-q...worksheet.html vba - How to preserve source formatting while copying data from word table to excel sheet using VB macro? - Stack Overflow
    Last edited by bigkahuna2187; 06-11-2014 at 09:06 AM.

  2. #2
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Bump.


    Anyone?

    Thanks

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

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Try:
    Please Login or Register  to view this content.
    Last edited by macropod; 06-06-2014 at 09:11 AM.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Please Login or Register  to view this content.
    David
    (*) Reputation points appreciated.

  5. #5
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    macropod, there is an error at line 17. Its compiles the first few tables but does not finish and gets an error at that line. It also does not put a blank lime between the tables. Close though amd thank you!

  6. #6
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    David, im getting a run time error (the remote server machine does not exist or unavailable) at "set oWordDoc)

  7. #7
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    This works on my machine.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    This did exactly what I was looking for, except it makes all of the Word tables into an image when being paced in excelis there a way to modify this so maintain the format/cells?

    Thanks again.

  9. #9
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    And is there a way to use the macro in the existing workbook instead of specifying the workbook?

  10. #10
    Forum Expert Tinbendr's Avatar
    Join Date
    06-26-2012
    Location
    USA
    MS-Off Ver
    Office 2010
    Posts
    2,125

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    I don't know of a way.

    I did find this similar thread from a year ago asking the same question.

  11. #11
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    i appreciate the help. +rep

  12. #12
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Any other suggestions?

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

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Quote Originally Posted by bigkahuna2187 View Post
    macropod, there is an error at line 17. Its compiles the first few tables but does not finish and gets an error at that line. It also does not put a blank lime between the tables.
    In my testing, the macro works fine on both counts. Contrary to what you claim, it does insert an empty row between the tables. If you're getting an error, that suggests some of the tables you are copying have merged cells and/or varying row heights or columns widths within the same row/column.

    You could try changing:
    iRow = iRow + .Rows.Count
    to:
    iRow = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

  14. #14
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    What would cause a run time error (paste method of worksheet class failed) at ActiveSheet.Paste Destination:=Range("A" & iRow)

  15. #15
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    That worked! Thanks! Disregard that last post now if I could only figure out how to place each table in an individual worksheet
    Last edited by bigkahuna2187; 06-09-2014 at 10:12 AM.

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

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Your first post - and everything you've posted till now - indicated you wanted all the data all on the same sheet. To only now say you want the data on different sheets is a great waste of everyone's time.
    Please Login or Register  to view this content.
    Last edited by macropod; 06-10-2014 at 12:45 AM.

  17. #17
    Registered User
    Join Date
    07-21-2011
    Location
    melbourne, florida
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Macro to Import Multiple Word Tables Into Excel Worksheet

    Not sure where that came from, but in my origional post I mentioned "An extra bonus would be to import each table within the Word document into individual Worksheets in Excel". I dont see where I indicated I wanted it on one page. Thank you for the help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Import tables from all Word documents within a folder into excel
    By Milk Snake in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-17-2014, 12:03 PM
  2. [SOLVED] Need a macro to copy multiple word files with tables into excel workbook
    By neetharao in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2012, 08:30 AM
  3. Import tables from multiple Word files into a sheet of Excel
    By magdy in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 03:53 AM
  4. Import cells from excel into word and create multiple word docs
    By scdaddy7269 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-20-2006, 03:03 PM
  5. Automating import of Word tables into Excel
    By Han in forum Excel General
    Replies: 0
    Last Post: 01-18-2006, 04:45 AM

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