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/join/brake-euro...
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/share/71GKke
#LearnExcelVBA #LearnExcelMacro

Пікірлер: 27

  • @robinwilliams1913
    @robinwilliams19134 жыл бұрын

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

  • @LearnExcelVBA

    @LearnExcelVBA

    4 жыл бұрын

    Glad it was helpful!

  • @ranajsingh
    @ranajsingh2 жыл бұрын

    It was superb Sir.. 🙏

  • @MohAboAbdo
    @MohAboAbdo9 ай бұрын

    Thank you so much

  • @sainaths1369
    @sainaths13692 жыл бұрын

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

  • @viswanathan19
    @viswanathan192 жыл бұрын

    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

    @LearnExcelVBA

    2 жыл бұрын

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

  • @robdav1es
    @robdav1es2 жыл бұрын

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

  • @alex45598
    @alex455982 жыл бұрын

    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

  • @HeelBusiness
    @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)

  • @ajaykumar-ml1dt
    @ajaykumar-ml1dt3 жыл бұрын

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

  • @CoderWave
    @CoderWave3 жыл бұрын

    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

  • @blancasanchez9765
    @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.

  • @sachinsavadatti7148
    @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.

  • @johnabram4159
    @johnabram41596 ай бұрын

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

  • @gitanjalisingh1167
    @gitanjalisingh1167 Жыл бұрын

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

  • @nyyamyrick9368
    @nyyamyrick93682 жыл бұрын

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

  • @LearnExcelVBA

    @LearnExcelVBA

    2 жыл бұрын

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

  • @girishmuddebihalkar3804
    @girishmuddebihalkar38043 жыл бұрын

    Hi it didn’t worked for me no files copied

  • @LearnExcelVBA

    @LearnExcelVBA

    3 жыл бұрын

    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

    @girishmuddebihalkar3804

    3 жыл бұрын

    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

    @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
    @jgcelis2 жыл бұрын

    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

    @zf485

    2 жыл бұрын

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

  • @jgcelis

    @jgcelis

    2 жыл бұрын

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