How To Extract Data From Word To Excel Using Vba

To extract data from Word to Excel using VBA, you can follow these steps:

1. Open Excel and create a new workbook.
2. Press Alt + F11 to open the VBA editor.
3. In the VBA editor, go to Insert > Module to create a new module.
4. In the module, write the following code:

Sub ExtractDataFromWord()
    Dim wordApp As Object
    Dim wordDoc As Object
    Dim excelApp As Object
    Dim excelWorkbook As Object
    Dim excelSheet As Object
    Dim wordFile As String
    Dim i As Integer
    Dim j As Integer
    
    ' Set the path of the Word document
    wordFile = "C:\path\to\word\document.docx"
    
    ' Create a new Word application
    Set wordApp = CreateObject("Word.Application")
    
    ' Open the Word document
    Set wordDoc = wordApp.Documents.Open(wordFile)
    
    ' Create a new Excel application
    Set excelApp = CreateObject("Excel.Application")
    
    ' Add a new workbook
    Set excelWorkbook = excelApp.Workbooks.Add
    
    ' Set the active sheet to the first sheet
    Set excelSheet = excelWorkbook.Sheets(1)
    
    ' Loop through the Word document paragraphs
    For i = 1 To wordDoc.Paragraphs.Count
        ' Loop through the words in each paragraph
        For j = 1 To wordDoc.Paragraphs(i).Range.Words.Count
            ' Copy the word to the Excel sheet
            excelSheet.Cells(i, j).Value = wordDoc.Paragraphs(i).Range.Words(j)
        Next j
    Next i
    
    ' Save and close the Excel workbook
    excelWorkbook.SaveAs "C:\path\to\excel\output.xlsx"
    excelWorkbook.Close
    
    ' Close the Word document
    wordDoc.Close
    
    ' Quit the Word and Excel applications
    wordApp.Quit
    excelApp.Quit
    
    ' Release the objects from memory
    Set wordApp = Nothing
    Set wordDoc = Nothing
    Set excelApp = Nothing
    Set excelWorkbook = Nothing
    Set excelSheet = Nothing
    
    MsgBox "Data extracted successfully!"
End Sub

Once you have written the code, you can run it by pressing F5 or by going to Run > Run Sub/UserForm.

Make sure to update the wordFile variable with the correct path to your Word document. Also, specify the path where you want to save the Excel output file in the excelWorkbook.SaveAs line.

The code uses the Word application object to open the Word document, and the Excel application object to create a new workbook. It then loops through the paragraphs and words in the Word document, copying each word to the corresponding cell in the Excel sheet. Finally, it saves and closes the Excel workbook, and quits the Word and Excel applications.

After running the code, you will receive a message box indicating the successful extraction of data from Word to Excel.

Hope this helps! Let me know if you have any further questions.