Macro to extract data from Word table to Excel | Excel VBA

[@sford7582]

Really useful in helping me automate some table extraction..thanks for sharing/posting.

[@ragulkrishnamoorthy2102]

Helped me with a small project, thanks for the video

[@henriqueto2011]

Excellent video. I will sub to this channel. Best regards from Brazil

[@jiteshvohra9876]

Really useful thanks. Like you have written i, i am able to use i+1 in sh.Cells but how to write 2i-2. Can you please help?

[@JoshuaWayt]

So much value in this video! Thank you so much!

[@elizabethhusain7572]

Great video . Nice job with that . Do you know if I can use macro to extract the list values from the drop-down list of some templates and put the list of options under the header for each column . There is not reference cells or table just the list values .

[@deepakkakara23]

Thank you so much, a very much useful video

[@dondiroberts]

Excellent video and instruction. I would like to combine this lesson with the lesson you indicated about extracting from multiple Word documents, which you stated was already on your channel. I just can't find it. Thank you again so much for the clear and concise instruction.

[@thomasnovak8226]

Were you able to find his lesson about extracting data from multiple Word documents? I can't find it either.

[@rockroll6414]

Just watched the few video and came up with this. Sub extractData() Dim wd As Word.Application Set wd = New Word.Application Dim doc As Word.Document Dim sh As Worksheet wd.Visible = True FolderName = "A:\AA HR\Aman\PAS 1 Nov 2021 - 30 April 2022\New folder\Final\test\" fileName = Dir(FolderName) Do While fileName " " Set doc = wd.Documents.Open(FolderName & fileName) Set tbls = doc.Tables Set sh = ActiveSheet lr = sh.Cells(Rows.Count, 1).End(xlUp).Row + 1 For i = 1 To 6 sh.Cells(lr, i).Value = Application.WorksheetFunction.Clean(tbls(2).Rows(i).Cells(2).Range.Text) Next doc.Close fileName = Dir() Loop wd.Quit Set doc = Nothing Set sh = Nothing Set wd = Nothing End Sub

[@usmansardarmuhammad961]

Hey Thank you, I helped me a lot. Stay Blessed.

[@abubakar343]

Thanks for the video. How can I prompt the user to select the word file and also how can we do it for mulitple documents.

[@rkhyami]

Thank you for the amazing video and code. I am not a programmer and trying to collect the first table info from multiple docs in a folder into an Excel sheet. Would you be kind to share the update to the code to collect from multiple word documents please?

[@limitone9564]

yes, I have a question... what if I want to extract a calendar.doc or calendar.docx from WORD (meaning I created a calendar of the month and on each day/particular square of the calendar is a date and on that date there are specific word such as "transplant". Now I need excel to capture that word "transplant" and the Date square (Dec 3, 2022) it was entered on to Excel sheet1 with names and ID number of that particular Word document. Each calendar Word doc is associated with an employee name and ID. hope I explained it well... thank you!

[@sugunalakshmanan2196]

Excellent

[@sanjayklal5354]

Thanks so much. Exactly what I was looking for. What if the word document has multiple pages but values from all the pages have to go to one sheet of excel? How to look for a page break and repeat the code for the page?

[@dochkei8535]

❤Thanks a lot! You saved my life!

[@anitalalwani8980]

Hi ,I do have plain text format with the data which I wish to extract in excel automatically , O am not a programmer so can you help me out for the same.

[@techtbe]

Great video Bro, It helped me lot. thanks

[@motoeplus2047]

Thank you sir ....it was much needed

[@kensonnicholas444]

How do I extract multiple files with the same format and place them into the same excel file, you made a reference to this scenario at the end of the video.Is there a video or specific line of code which can help me with my question

[@syms1090]

Thank you sir, this video is great. I will be looking at the rest of your channel to learn more about using VBA in the future. You mentioned at end of video about extracting from multiple word documents, could you please link that video so others and I can find it easier? This video, plus that video will cover 90% of my needs! FOR INFO IF CURIOUS: For my current task, I have multiple word files that have the same templates, same tables, etc. The files all have different names, so I want to be able to put them all in a single folder and extract the file name into A3 then data from that files 3rd table into B3-H3 then move onto next word document and extract name and data into next row, until all word files have been processed. Then maybe a splash screen to say how many word files had been processed would be a nice end to it, but that would be optional :-)

[@jaydriscoll9555]

Hi, using your sample data, if you had more columns, how would you get the code to loop to copy the next columns' data into new rows in Excel? Is there a loop function to do this?

[@bonniebaker6226]

Thanks for the video. My word document also contains pictures. Is there an easy way to extract the pictures and add them to excel at a particular cell location? I'm currently using copy and paste but I'd like something faster that doesn't rely on the clipboard.

[@sharmilachaudhari7111]

I have a 100+ tables in word file with 2 columns where 1st columns rows are same for all tables and 2nd columns has values for each row of 1st column. How to get it in excel?

[@kellykay9145]

Excellent video! How can I determine if the color of a row in a Word table is red or not? I want to return the value 1 if RED.

[@nisha510]

Hiee... if i have unstructured data in ms word how can i extract specific data into excel?

[@xtremeExcel]

Give me an example

[@hugofernandes8189]

@@xtremeExcel Like if you wanna extract all phone numbers but they are not in a specific place on the text (like a table), but they're can be along all file/document/text (not organized or structured). Regards from Brazil and thanks for sharing your knowledge o/

[@hugofernandes8189]

If all phone numbers are preceded by a specific name like "phone" or have a specific format like "phone xxxx-xxxx", would it be easier?

[@CoderWave]

I want to copy table from word where table has very large data in 1 cell which has some links and data formatting in it ...is there any way to copy as it is in excel

[@rafaelrivera9291]

Is there any way to asign the number of rows you want to analyse to always be equal to the number of cells in the word table?

[@xtremeExcel]

Yes we can surely do. Please share the sample doc and code you have written. I'll help you out from there( I do not have the files handy with me)

[@p.r.chandru3125]

hi, i have 100 page word document and its contains text and also 100 tables with text, how do i copy only text from the 100 tables into excel, help me

[@devendrasingh521]

Hi, how can I extract word's header footer to excel

[@xtremeExcel]

Need more precise details

[@srinivassri7067]

Hello, How can i extract multiple tables from a word document into excel. the word file may contain more than 10 tables but i want to extract only few specific tables.

[@xtremeExcel]

Yes it ia possible. In this video, have you noticed that I have used the table index?

[@srinivassri7067]

@@xtremeExcel Yes, the table index is used because there are only 2 tables in the file.in my scenario there are nearly 60 tables out of which i want to extract 5-6 tables only. Cant the user select the different tables that they want to extract & user may not know the number of rows present in it.

[@unbox4253]

Hi Kamal, this does not work for my word table with vertically merged cells. Any suggestion?

[@xtremeExcel]

Please share you file. Details are on my channel homepage..

[@viswanathan19]

How to change the properties of multiple word/excel/ppt files on a click using VBA by extraction of properties in an excel and hen changing it as desired

[@fyi9024]

I need to convert more word files with tables of same template in a directory can u pls help me out with the script

[@xtremeExcel]

sure. please write to me with details

[@stefaniecheng]

Is it possible to transfer the content table-within table from word into excel using vba?

[@mertthew1084]

Hi Kamal, This video was just what I needed. But is there a bit of code that can be added to create a new worksheet for each table? I have a lot of tables in a word document that I want to extract into different worksheets in the same workbook. They are all set out the same with the same number of columns. Thanks.

[@xtremeExcel]

yes. this can be done. Do you need my help in that?? I suggest to try that once and share your file with me in it doesn't work for you..

[@adrianleong5848]

Can this be done on MS teams? Example, there are Word Doc on MS teams and I would like to set excel to grab data from the Word Doc. How would the set up in VBA look?

[@selvan1264]

Thank you for the nice video. How to extract data from an email which has this type of similar table into excel columns?

[@xtremeExcel]

Hi Selva, You can use Microsoft HTML Object Library in this case. You need to get the htmlbody and then you can navigate through the table inside that html document and iterate on rows and columns.

[@selvan1264]

@@xtremeExcel thank you for the reply. By any chance do you happen to have any related video on it for extracting using html object library? Thank you

[@jgcelis]

Hello Kamal, thank you for such a wonderful video. I do have a questions though. For some reason I keep getting a Run-time error '91': "Object variable or With block variable not set", and the debugger highlights the "Set tbls = doc.Tables" line. However, this only happens after I have saved the file as "Macro-Enabled Workbook". It runs perfectly fine when I take the EXACT same code and paste it into a brand new file/module. But again, once I save it and try to run it again it just keeps giving me the "Run-time" error. Thank you in advance.

[@balugopal2944]

Can you share me the VBA code for "Copy the header and footer data from ms word to excel file"

[@mikekennedy8751]

Hi Kamal, When the Table rows size varies, the Macro crashes. Is there anyway to tell the macro the size of the word table(how many rows), so this can be a variable within the macro? Thanks very much for your videos. They are great! Cheers.

[@deepakbhanushali1]

You have give file of word file what if path and file name mention in cell value and it should open that file

[@xtremeExcel]

Sorry. I do not understand your question completely. Can you share more details please?

[@deepakbhanushali1]

@@xtremeExcel you have given file name in path what if that file name and path it picks up from cell where path and file name are given because everytime file name cannot be same

[@pradumnajaiswar6017]

Multiple word doc then what can we do

[@xtremeExcel]

Use a for loop Inside that, open doc, extract data and close each doc one by one

[@wonsilla]

Thank-you.

[@ramanabooshan8757]

To be honest , ur video helped a lot with my project. But I have a doubt , how can I extract two separate cell's word from word doc and input it in a single cell of excell . Eg: (cell1 / cell2) . Thanks in advance.

[@KhalilYasser]

Great. Thanks a lot.

[@xtremeExcel]

So nice of you!

[@ceyhunkvrak7791]

👏👏