VBA Macro Code - Copy Data from Multiple Word Files to Excel

In this video, you will learn how you can Copy Data from Multiple Word Files to Excel using VBA Macro Code.
VBA Macro Code Used in the video:
Sub Copy_Data_From_Multiple_WordFiles()
Dim FolderName As String
Dim FileName As String
Dim NewWordFile As New Word.Application
Dim NewDoc As New Word.Document
Application.DisplayAlerts = False
'Application.ScreenUpdating = False
FolderName = "C:\Test\"
FileName = Dir(FolderName)
'Loop start
Do While FileName ⋖⋗ ""
Set NewDoc = NewWordFile.documents.Open(FolderName & FileName)
NewDoc.Range(0, NewDoc.Range.End).Copy
Range("LastRow").PasteSpecial xlPasteValues
NewDoc.Close SaveChanges:=wdDoNotSaveChanges
NewWordFile.Quit
FileName = Dir()
Loop
End Sub
-------
Video Gears
Mic used for Audio recording: amzn.to/32ZSagv
Soundproofing Foam that I use, this also helps to reduce echo: amzn.to/2Dy939l
For Video editing, I use Canva.com, you can join Canva by clicking here: www.canva.com/....
Computer used for Editing: amzn.to/3bv9PjL
Mobile used for video shooting: amzn.to/3gZP00X
Reach out to me on Fiver for any excel automation projects: www.fiverr.com...
#LearnExcelVBA #LearnExcelMacro

[@robinwilliams1913]

Thanks for sharing. Nicely explained. This is very useful. I was looking for such code for my project.

[@LearnExcelVBA]

Glad it was helpful!

[@ajaykumar-ml1dt]

How to copy only specific contents under the heading in the file for multiple word files to excel?

[@viswanathan19]

explanation is really great, thank you for the video. Help needed on my query is, how to copy data from a template and develop a table out of it

[@LearnExcelVBA]

Glad it was helpful! I havent tried that yet. I will check and get back to you.

[@sainaths1369]

Good explanation concept was easy to understand. Sir could you pls explain how make the folder name and file name dynamic

[@ranajsingh]

It was superb Sir.. 🙏

[@HeelBusiness]

Hello - I also come across error 462: the remote server machine does not exist or is unavailable. This happens after looping (the first run-through works perfectly)

[@robdav1es]

Can the macro be modified to pick out a section from the word documents?

[@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

[@MohAboAbdo]

Thank you so much

[@johnabram4159]

Bro, the code keep pasting the same file. Doesn't loop.

[@sachinsavadatti7148]

I followed the same procedure and path but is is fetching data of only one word file. Remaining word file details it is not fetching. Can you help me on this.

[@alex45598]

Hi thanks for your video! For some reason I keep running into an automation error at the "Set NewDoc=newWordFile.Documents.Open(FOldername & FileName)" any help? thanks

[@blancasanchez9765]

The code worked but it does not find the last row it copies the subsequent document data just a few rows down and no after the last row.

[@nyyamyrick9368]

I got a message "the remote server does not exist or unavailable". Any tips on what to do next?

[@LearnExcelVBA]

Please Check that file paths are correct and accessible when running the code.

[@gitanjalisingh1167]

I am running the code but it is not giving any result nor showing error

[@girishmuddebihalkar3804]

Hi it didn’t worked for me no files copied

[@LearnExcelVBA]

Hi Girish, Thanks for watching the video. Kindly make sure that file path is correct and it is ending with "\" & References under Tools should have Miscrosoft Word selected. If you have followed all the steps mentioned in the video. Kindly share what error you are getting.

[@girishmuddebihalkar3804]

I hv 2 to 3 tables n many data in word files n I am trying to get data of two files in a folder . Done as per your videos but it’s just running for long time n not getting any out put.

[@rexy2396]

Hi sir can I get the data from one word file to one sheet and another file to another sheet of excel. And so on

[@jgcelis]

Hello, Thank you for posting such an awesome video. After hitting a couple of speed bumps I realized that I had to comment the line "NewWordFile.Quit" because for some reason it was giving me an error "Run-time error '462' - The remote server machine does not exist or is unavailable". Now I have been able to run the code just fine but I do have a question though. Is there a way to copy and paste an entire document into a single excel cell? For instance, if I were to have 5 MS Word documents in my file path, how would I go about pasting each document content into a single cell. Basically, 5 MS documents = 5 different rows. eg. A1, A2, A3, A4, A5. Thank you!

[@zf485]

Hi, I am getting the same error message. Where in the code did you add that additional line, of "NewWordFile.Quit"? Thanks!

[@jgcelis]

@@zf485 Hello, I commented the line. Basically removed it from the code. I hope it helps