VBA Macro Request (Word and Excel)

Ethan2465 

To accomplish this task, you can use VBA macros in both Word and Excel to automate the extraction of information from multiple Word documents and populate an Excel spreadsheet with the extracted data. Below are the steps to achieve this:

Word VBA Macro:

1. Open one of the Word documents from the folder where you need to extract the information.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. Go to Insert > Module to insert a new module.
4. Copy and paste the following VBA code into the module:

Vba code is untested, please backup your file.

Sub ExtractDataFromWord()
    Dim wrdApp As Object ' Word.Application
    Dim wrdDoc As Object ' Word.Document
    Dim strFile As String
    Dim iRow As Long

    ' Initialize Excel
    Dim xlApp As Object ' Excel.Application
    Dim xlWb As Object ' Excel.Workbook
    Dim xlWs As Object ' Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Sheets(1)

    ' Specify the folder path containing Word documents
    strFolder = "C:\YourFolderPath\" ' Update this with the path to your folder
    strFile = Dir(strFolder & "*.docx") ' Modify the file extension if necessary

    ' Loop through each Word document in the folder
    Do While strFile <> ""
        ' Open the Word document
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Visible = False
        Set wrdDoc = wrdApp.Documents.Open(strFolder & strFile)

        ' Extract data and write to Excel
        iRow = xlWs.Cells(xlWs.Rows.Count, 1).End(-4162).Row + 1 ' Find the next available row in Excel
        xlWs.Cells(iRow, 1).Value = wrdDoc.Range.Paragraphs(1).Next.Paragraphs(1).Range.Text ' Extract Name
        xlWs.Cells(iRow, 2).Value = wrdDoc.Range.Paragraphs(2).Next.Paragraphs(1).Range.Text ' Extract Date

        ' Close the Word document
        wrdDoc.Close False
        wrdApp.Quit

        ' Move to the next Word document
        strFile = Dir
    Loop

    ' Cleanup
    Set wrdApp = Nothing
    Set wrdDoc = Nothing
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlWs = Nothing

    MsgBox "Data extraction completed!", vbInformation
End Sub

5. Modify the following parts of the code:

• Replace "C:\YourFolderPath\" with the path to the folder containing your Word documents.

6. Close the VBA editor.

Excel VBA Macro:

1. In Excel, open a new workbook.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. Go to Insert > Module to insert a new module.
4. Copy and paste the following VBA code into the module:

Vba code:

Sub ExtractDataFromWordDocuments()
    Dim objWord As Object ' Word.Application
    Dim objDoc As Object ' Word.Document
    Dim strFolder As String
    Dim strFile As String
    Dim iRow As Long
    
    ' Initialize Excel
    Dim xlApp As Object ' Excel.Application
    Dim xlWb As Object ' Excel.Workbook
    Dim xlWs As Object ' Excel.Worksheet

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWb = xlApp.Workbooks.Add
    Set xlWs = xlWb.Sheets(1)

    ' Specify the folder path containing Word documents
    strFolder = "C:\YourFolderPath\" ' Update this with the path to your folder
    strFile = Dir(strFolder & "*.docx") ' Modify the file extension if necessary

    ' Loop through each Word document in the folder
    Do While strFile <> ""
        ' Open the Word document
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False
        Set objDoc = objWord.Documents.Open(strFolder & strFile)

        ' Extract data and write to Excel
        iRow = xlWs.Cells(xlWs.Rows.Count, 1).End(-4162).Row + 1 ' Find the next available row in Excel
        xlWs.Cells(iRow, 1).Value = objDoc.Range.Paragraphs(1).Next.Paragraphs(1).Range.Text ' Extract Name
        xlWs.Cells(iRow, 2).Value = objDoc.Range.Paragraphs(2).Next.Paragraphs(1).Range.Text ' Extract Date

        ' Close the Word document
        objDoc.Close False
        objWord.Quit

        ' Move to the next Word document
        strFile = Dir
    Loop

    ' Cleanup
    Set objWord = Nothing
    Set objDoc = Nothing
    Set xlApp = Nothing
    Set xlWb = Nothing
    Set xlWs = Nothing

    MsgBox "Data extraction completed!", vbInformation
End Sub

5. Modify the following parts of the code:

• Replace "C:\YourFolderPath\" with the path to the folder containing your Word documents.

6. Close the VBA editor.

Running the Macro:

1. In Excel, press Alt + F8 to open the "Macro" dialog.
2. Select the macro you want to run (e.g., ExtractDataFromWordDocuments) and click "Run".

This macro will loop through all the Word documents in the specified folder, extract the Name and Date information, and populate an Excel spreadsheet with the extracted data. Make sure to adjust the file path and extensions as necessary. The text, steps and the code were created with the help of AI.

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.