+ Reply to Thread
Results 1 to 26 of 26

Extract word tables (multiple files) into excel using VBA

  1. #1
    Registered User
    Join Date
    12-16-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    1

    Extract word tables (multiple files) into excel using VBA

    Hello,

    I would greatly appreciate if someone is able to help me with my challenge to automate a lot of manual effort.

    I have a large quantity of Word 2010 documents where I want to extract the table information from each document and put it into Excel. Instead of manually doing this, I'm hoping there is a way to automate this using VBA in Excel.

    Each word document contains 1 table, with the same layout: 2 columns, 20 rows. The first column is the same in every document, the 2nd column contains the data I want.

    I'm looking to run a macro from Excel that would do the following:
    1. Automatically open each Word doc (1 at a time). NOTE: All word documents and the Excel file would reside in the same folder
    2. Extract the data from the 2nd column of the word table and paste it into Excel (transpose the column into a row, so I can treat all the data like a file)
    3. In the last (or first cell) per row, add the Word doc file name
    4. Close the current word document, then open the next one in the folder and repeat above steps

    So if I had 200 Word documents, then 200 rows would be imported into Excel.

    I have code to do this for Excel documents (merging all the Excel files within a folder into 1 document), but have no idea how to do this for Word.

    Thanking you in advance for your assistance.

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

    Re: Extract word tables (multiple files) into excel using VBA

    Copy this to a standard module.
    Please Login or Register  to view this content.
    Last edited by Tinbendr; 12-19-2014 at 02:54 PM.
    David
    (*) Reputation points appreciated.

  3. #3
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    This is an incredible macro but is there away of extracting other columns in the table onto the same line as well as additional tables in the word document some on seprate pages?

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

    Re: Extract word tables (multiple files) into excel using VBA

    Sure. Details, please.

  5. #5
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    Thanks,

    I've attached an example Word document my scenairo is the same as above but I want to extract more than one column and have it listed after the last entry of the first column. So the row would look like this:

    Joe Bloggs, 12, Male, 18, Penny Lane, Sachs Ville, Hampshire, England, Football, Exampl Data

    I am also looking to extract a second table as depicted in the attached Word document which can be on a seprate page.

    I may well be asking for the moon but I am hoping this is achievable as I have thousands of legacy documents to go through.
    Attached Files Attached Files

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

    Re: Extract word tables (multiple files) into excel using VBA

    So, you want Word Table 1 on Excel Sheet1, Word Table 2 on Excel Sheet2?

  7. #7
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    Ideally all in the same row on the same sheet.

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

    Re: Extract word tables (multiple files) into excel using VBA

    I changed it up a little in that you browse for a file location and select the files you want to process. It's been my experience to only select 50-100 files to work on at a time. Keeps Word from bogging things down.

    Also, after I created a few extra files for testing, the first name and surname get separated on the Excel sheet due to the row order verses column order.

    The way it's written now, it can handle tables that have different column counts.

    Hope this helps!
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    Alas I get an error "The requseted member of the collection does not exist" I hit debug and get the following code line highlighted:

    Please Login or Register  to view this content.
    Any thoughts?

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

    Re: Extract word tables (multiple files) into excel using VBA

    It's probably an uneven row count. Change this line from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    It will import the headers as well, but at least you'll have your data.

  11. #11
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    Nope I am afraid still the same error, would a page break before the tables cause the error?

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

    Re: Extract word tables (multiple files) into excel using VBA

    Can you send me the offending file? My username @ google mail

  13. #13
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    I've sent the form to you hopefully my explanation makes sense, it seems it's one table but looks like two as it is spread over two pages and their is also some merged cells in the form.

    Is it possible to also include .docx files as well in the scan?

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

    Re: Extract word tables (multiple files) into excel using VBA

    OK, give this a go.
    Please Login or Register  to view this content.
    Last edited by Tinbendr; 09-14-2015 at 08:52 PM.

  15. #15
    Registered User
    Join Date
    08-24-2015
    Location
    Emsworth, England
    MS-Off Ver
    2019
    Posts
    27

    Re: Extract word tables (multiple files) into excel using VBA

    FANTASTIC!

    Thanks Tinbendr.

    Wonderful piece of programming, works like a charm. Just run it with fantastic results a whole list of data with a little bit of tweaking a perfect list for my needs. A thousand thanks!

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

    Re: Extract word tables (multiple files) into excel using VBA

    I'm glad we got it sorted.

    Cheers!

  17. #17
    Registered User
    Join Date
    11-07-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract word tables (multiple files) into excel using VBA

    Hi there... how i wish i could get this to work for me! When I run this I receive an error as follows:

    "run-time error 13 - type mismatch"

    I'm using Office 2013.....

    Can anyone help please?

    thank you


    j

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

    Re: Extract word tables (multiple files) into excel using VBA

    Quote Originally Posted by 10degrees View Post
    When I run this I receive an error as follows:
    "run-time error 13 - type mismatch"
    Care to share with us where the error occurs?

  19. #19
    Registered User
    Join Date
    11-07-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Extract word tables (multiple files) into excel using VBA

    aah what a good idea Tinbendr! My apologies...

    Error occurs here:

    If FN = False Then GoTo TheEnd

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

    Re: Extract word tables (multiple files) into excel using VBA

    Try this line instead
    Please Login or Register  to view this content.

  21. #21
    Registered User
    Join Date
    12-13-2018
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    2

    Re: Extract word tables (multiple files) into excel using VBA

    Very very thank you Mr. Tinbendr

    You are Genious,
    You provide a excelent code
    It saved me a lot of time
    It can be manipulated
    VERY VERY NICE SIR
    THANK YOU AGAIN

  22. #22
    Registered User
    Join Date
    12-13-2018
    Location
    Pakistan
    MS-Off Ver
    2013
    Posts
    2

    Re: Extract word tables (multiple files) into excel using VBA

    Excellent Work,

    Sir, this code contain extraction of table rows without first header row, what would be change if i want to include first header row also because i faced many of such type of tables.

    Waiting for good response

    Thanking in advance
    Javed

  23. #23
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,946

    Re: Extract word tables (multiple files) into excel using VBA

    javedchemist2245 welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  24. #24
    Registered User
    Join Date
    01-26-2024
    Location
    tehran
    MS-Off Ver
    2019
    Posts
    1

    Re: Extract word tables (multiple files) into excel using VBA

    dude , youve just saved my life , just want you to know , thanks

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

    Re: Extract word tables (multiple files) into excel using VBA

    Glad my efforts are still helping people.

  26. #26
    Forum Contributor
    Join Date
    06-12-2009
    Location
    Banglore
    MS-Off Ver
    Excel 2007
    Posts
    129

    Re: Extract word tables (multiple files) into excel using VBA

    Hi Tinbendr,

    Will you be able to modify your code to accommodate my request given below?

    https://www.excelforum.com/excel-pro...ml#post5943711
    Shan

+ 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. Replies: 2
    Last Post: 07-18-2013, 02:42 AM
  2. URGENT - Extracting / Importing certain tables from several word Files to Excel
    By pareshvm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2013, 09:29 AM
  3. [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
  4. Extract headers from multiple Word files and list them in Excel
    By Towanda in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-30-2012, 12:09 PM
  5. 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

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