Copy data or content from Word file to Excel using VBA

← Prev

Let us assume, I have a word file with few paragraphs in it. I want to copy the content as it is from the doc file and write it in my Excel worksheet, each word in a cell, go to the next line or row and write again. Does it sound interesting? I’ll show you how to do this with the click of a button using a simple macro in VBA.

I don’t know how useful it would be for you. I wrote this simple macro for my wife who is teacher. She asked me if there’s a way she can copy and paste contents from a word file to Excel. Since it’s a repeated job, I said we can automated the whole process using VBA. However, just make sure, that the contents are written properly in the word file, that is, the spaces, the next line (carriage return) etc. is properly done.

See this image. 👇

Copy data from word file to Excel worksheet using VBA

Here's what I am doing!

In this example, I have a button, an ActiveX Control in my worksheet (sheet 1). Clicking the button will open a File Picker dialog box, so you can select the word file you want.

Once you have selected the word file, the macro will open the doc, read and extract data (content) from the file and write it in your Excel worksheet.

Note: The file picker or file dialog is optional. You can provide the file name and path to the variable sfileName.

Add a Reference

Press Alt+F11 to open the VBA editor. You can also right click sheet1 and choose View Code option. Add Office Object Library Reference to your application. See the image.

Add Office Object Library Reference

The VBA code or the Macro
Option Explicit

Private Sub CommandButton1_Click()
    extract_word_data
End Sub

Public Sub extract_word_data()
    On Error Resume Next
    
    ' Create a "FileDialog" object as a File Picker dialog box.
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    Dim sfileName As String
    
    With fd
        .AllowMultiSelect = False
        .Filters.Clear
        .Title = "Select a Word File"
        .Filters.Add "All Word Documents", "*.doc?", 1
    
        If .Show = True Then
            sfileName = Dir(.SelectedItems(1))      ' Get the file.
        End If
    End With
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    If Trim(sfileName) <> "" Then
        Dim objWord As Object       ' Create a Word object.
        Set objWord = CreateObject("Word.Application")
        objWord.Visible = False      ' Do not show the file.
        
        ' Create a Document object and open the Word file.
        Dim objDoc
        Set objDoc = objWord.Documents.Open(fd.InitialFileName & sfileName)
        
        Dim sPara() As String
        sPara = Split(objDoc.range, vbCr)	' split the carriage returns and store in the variable.
        
        Dim iCnt, iCnt1, iRow As Integer
        iRow = 1
        
        For iCnt = 0 To UBound(sPara)
            Dim str() As String
            str = Split(sPara(iCnt), " ")		' split the spaces and store in the variable.

            For iCnt1 = 0 To UBound(str)
                Sheet1.Cells(iRow, iCnt1 + 1) = str(iCnt1)		  ' Write data.
            Next iCnt1
            iRow = iRow + 1
        Next iCnt
    End If
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ' Clean up.
    objWord.Close
    objWord.Quit
    objDoc.Quit
    Set objWord = Nothing
    Set objDoc = Nothing
End Sub

Note: The file picker or file dialog is optional. You can provide the file name and path to the variable sfileName.

The code above has comments to help you understand the functions.

The above example has some limitations, of-course. For example, it may not translate the tab spaces properly, that is, if you have used the tab multiple times to shift a particular string from left to right etc. However, it will read the spaces and carriage returns (for next line) well.

Copy table data from Word file to Excel Worksheet (as it is)

Now, a word file or doc may have tables (multiple tables). You can read those tables as it is using a simple macro. Here’s the example.

You can test the above code using big word files, with many paragraphs, tables and other objects.

Thanks for reading.

← Previous