Wise Owl Answers - How do I copy Word tables into Excel using VBA?

[@KawanExcel]

This chanel deserve for more view (y) Thank You, Andrew

[@WiseOwlTutorials]

You're very welcome, thank you for the comment and for watching!

[@yubarajpaudel4779]

This code is not working with mine files,

[@youssefsedkey7616]

It was a great tutorial and I benefited so much, Thank you Andy.

[@WiseOwlTutorials]

Thank you Youssef, happy to hear that you found it useful!

[@RohithKK-uh7pp]

Very wonderful. Excellent video. Thank you.

[@WiseOwlTutorials]

Thank you as always Rohith!

[@hazimmerghani6625]

Thanks for this video, i have a question : can i copy the tables from word with the same format and same cells dimensions?

[@frikduplessis8849]

Thanks Andrew, you make it look so easy 👍😀

[@WiseOwlTutorials]

Thanks Frik, happy to hear that you enjoyed it and thanks for your comments as always!

[@TANJIMKALEKHAN]

can we paste merged cells as it is in Excel?

[@krn14242]

Thanks Andrew.

[@WiseOwlTutorials]

My pleasure Kevin!

[@soumyayadav6509]

Does this code paste all tables into excel

[@henrikijonkoping4694]

Great, very useful!

[@WiseOwlTutorials]

Thanks Henrik, I appreciate the support!

[@suhaschauhan3511]

Hi, This video is really great !! Could you please add script for taking user input? For example, I would like it to extract tables from pages 4-10 if there are 50 pages in the document. Both 4 and 10 Should be entered by the user when the macro runs.

[@WiseOwlTutorials]

Hi Suhas! We have several videos on using a VBA inputbox or a user form for accepting user input. You can search for inputbox videos from the Wise Owl channel homepage. I hope it helps!

[@BokyJ]

Hello, How can we copy entire text (headings and paragraphs) from word doc to excel cells? Thanks in advance.

[@WiseOwlTutorials]

Hi, you can copy the range of the document like so Sub ImportWholeDoc() Dim wd As Word.Application Dim doc As Word.Document Dim tbl As Word.Table Dim ws As Worksheet Set ws = Worksheets.Add Set wd = New Word.Application wd.Visible = True Set doc = wd.Documents.Open(ThisWorkbook.Path & "\Movies.docx") doc.Range.Copy ws.Paste doc.Close wd.Quit End Sub

[@dukestt5436]

Can you add data from a table to an excel sheet, but from an outlook template triggered from the "send email" event, I'm close but for some reason it crashes when I try to close the instance of the workbook

[@WiseOwlTutorials]

It certainly sounds as though it should be achievable and it seems like an odd place for your code to crash. Are you able to update the table successfully in Excel if you don't attempt to close it?

[@dukestt5436]

@@WiseOwlTutorials ok something odd is going on , it only closes and copies if the TRUE clause is in the close statement, for saving the file and then of course it prompts to save. I'm getting closer though, I thought there was a way you could do it whilst the workbook was open, that would be the best solution.

[@WiseOwlTutorials]

@@dukestt5436 What happens if you add code to save the workbook before closing it?

[@dukestt5436]

@@WiseOwlTutorials ok, i got it working, the connection I mean I will try for the table next. Not sure now to get a reference to that, might have to use the inspector!! Thanks for your time , as always great videos.

[@WiseOwlTutorials]

@@dukestt5436 Excellent! I'd be interested to hear about your progress when you get it working!

[@jgcelis]

Hello, thank you for such great video(s). I actually have a situation in which I need to copy the content of each table in Word and paste it in excel in a single cell (one table = one cell) just as plain text. So, if I have 3 tables in word, I need them to be pasted in 3 different cells E.G. A1, A2 and A3. I actually have a vba code that does exactly that but the problem I have is that for some reason I cannot save the macro enabled file and run it more than once. I can run this code (see below) perfectly fine, but once I save the file and reopen it to run it on a different occasion it just gives me a "Run-Time error '91" - Object Variable or With Block Variable not set". This highlights the line "Set tbls = doc.Tables". I assume is because I have to declare the object variable but I have tried several options and nothing seems to stick. In any case, the code I have is the following, and I would very much appreciate your help. Sub copyData() Dim wd As New Word.Application Dim doc As Word.Document Dim sh As Worksheet wd.Visible = True Set doc = wd.Documents.Open(ActiveWorkbook.Path & "C:\Users\john\Desktop\Test.docx") 'For easy access, place Word file on the desktop Set tbls = doc.Tables Set sh = ActiveSheet 'MsgBox tbls.Count 'Uncomment to check for number of tables and comment EVERYTHING below except for "End Sub" For i = 1 To 4 'To = Number of Tables in the word document - Assign "To" the value given in the message generated from the line of code above 'sh.Cells(Row, Column). For the line of code below, in the case of “(i + 0, 1)" where i = 1, it places the first line of returned results in row "1". Where "i + 0" is the row placement (1), and "1" is the column placement (A). Hence the results placement starts at cell “A1” sh.Cells(i + 0, 1).Value = Application.WorksheetFunction.Clean(tbls(i).Rows(1).Cells(1).Range.Text) Next i doc.Close wd.Quit Set doc = Nothing Set sh = Nothing Set wd = Nothing MsgBox "Process completed successfully" End Sub

[@WiseOwlTutorials]

Hi John! I would change this line: Set doc = wd.Documents.Open(ActiveWorkbook.Path & "C:\Users\john\Desktop\Test.docx") To something more like this: Set doc = wd.Documents.Open("C:\Users\john\Desktop\Test.docx") I hope it helps!

[@jgcelis]

@@WiseOwlTutorials That did it! THANK YOU VERY MUCH!!!!!

[@Vikram471993]

How to copy Word tables based on page numbers? Fog eg, A Word document containing 100 pages and I need to copy only the tables from the user entered page number.

[@WiseOwlTutorials]

Hi Vikram, this feels like a non-optimal solution but one way is to loop through all the tables in the document and check the page number that the table is on. If the page number matches the one you have asked for then copy it into Excel: MyPageNumberVariable = 3 For Each tbl In doc.Tables If tbl.Range.Information(wdActiveEndPageNumber) = MyPageNumberVariable Then tbl.Range.Copy Set ws = ThisWorkbook.Worksheets.Add ws.PasteSpecial "HTML" ws.Range("A1").CurrentRegion.EntireColumn.AutoFit End If Next tbl

[@alivali7263]

Çok güzel olmuş

[@WiseOwlTutorials]

Teşekkür ederim

[@shiguehashimoto1247]

How can I copy all Textboxes Values from Word Document into Excel, instead of Tables? I am Brazilian, sorry for English mistakes.

[@romanemul1]

I wonder who may ask this. The code is so predictable.

[@WiseOwlTutorials]

I think that perhaps it feels easy because you have lots of experience with VBA already? Remember: we were all beginners at one time 😀

[@abdulazizalhumoud1176]

Hello, wonderful video! I was trying to apply the same concept; however, the word document path is a variable and i am getting the runtime error 4198. I would highly appreciate it if you can share a solution. the code is as follows: Dim filetoopen As Variant Dim wd As Word.Application Dim doc As Word.Document Dim tbl As Word.Table Dim ws As Worksheet Dim i As Integer i = 1 Set wd = CreateObject("Word.Application") wd.Visible = True filetoopen = Application.GetOpenFilename(Title:="Browse for your file & import range") Set doc = wd.Documents.Open(ThisWorkbook.Path & "filetoopen") Thank you so much