VBA - Word Document to Excel Spreadsheet.

I have quite a few Word Documents that I need to export into Excel Spradsheets for easier input into a database. I am not very familiar with VBA.

All the Word Documents are identical to the one attached. The Excel Spreadsheet would need the "Monitoring Point I.D." as the Key field, and the table as the remaining data. There is a seperate sheet for each Monitoring ID.
Mod-08-Att-8.13A-Springs.docx
GravitaZ24Asked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
Not sure if this is what you need. It is Word VBA macro code
Sub TablesToExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim tbl As Word.Table
    Dim r As Integer
    Dim c As Integer
    Dim t As Integer
    Dim strMonitorintPointID As String
    
    Set xlApp = New Excel.Application
    Set xlWbk = xlApp.Workbooks.Add
    xlApp.Visible = True
    Set xlWks = xlWbk.Sheets(1)
    t = 2
    Do Until t > ActiveDocument.Tables.Count
        If xlWbk.Sheets.Count < t / 2 Then
            Set xlWks = xlWbk.Sheets.Add
        Else
            Set xlWks = xlWbk.Sheets(t / 2)
        End If
        Set tbl = ActiveDocument.Tables(t)
        strMonitorintPointID = tbl.Cell(1, 2).Range.Fields(1).Result
        xlWks.Name = strMonitorintPointID
        Set tbl = ActiveDocument.Tables(t - 1)
        xlWks.Cells(1, 1).Value = "Monitoring Point I.D"
        For c = 1 To tbl.Columns.Count 'startcolumn to end column
            xlWks.Cells(1, c + 1).Value = GetCellText(tbl.Cell(1, c))
        Next c
        r = 2
        Do While True
            If Asc(tbl.Cell(r, 1).Range.Fields(1).Result) = 32 Then
                Exit Do
            End If
            xlWks.Cells(r, 1).Value = strMonitorintPointID
            For c = 1 To tbl.Columns.Count
                xlWks.Cells(r, c + 1).Value = tbl.Cell(r, c).Range.Fields(1).Result
            Next c
            r = r + 1
        Loop
        t = t + 2
    Loop
    'xlWbk.Close
    'xlApp.Quit
End Sub


Function GetCellText(cl As Cell) As String
    Dim rng As Range
    
    Set rng = cl.Range
    rng.MoveEnd wdCharacter, -1 'drop cell formatting
    GetCellText = rng.Text
End Function

Open in new window

0
 
scifo_dkCommented:
GrahamSkan:
Nice code!

Gravitaz24:
If you are not familiar with VBA, you need to know that the above code will only work if you add the Excel reference library.
When you're in the Visual Basic window, select "Tools" and then "References...".
Put a mark in "Microsoft Excel 12.0 Object Library" and click "OK".

The reason why you need to do this is that it uses Excel-specific code, and in order to access that code library, you need to enable it this way.

//Scifo_dk
0
 
GrahamSkanRetiredCommented:
Thanks scifo_dk.

Yes that method is called early binding. It is much easier for development and slightly faster to run, but might have compatibility issues between different releases.

The alternative is called late binding, for which to code would be the same except for the first three lines, which would become:

    Dim xlApp As Object
    Dim xlWbk As Object
    Dim xlWks As Object
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
GravitaZ24Author Commented:
Thank You guys so much! I seem to be getting an error though. Most likely something I'm doing wrong
 Error
0
 
GravitaZ24Author Commented:
It seems to be running perfectly and filling the sheets one at a time, but then that error pops up
0
 
scifo_dkConnect With a Mentor Commented:
From what I can tell, it looks like it is trying to rename a sheet in the excel workbook to the same name as another sheet.
Are there duplicates in the "Monitoring Point I.D." word-file?
0
 
GrahamSkanRetiredCommented:
To check, try commenting out line24:

      ' xlWks.Cells(1, 1).Value = "Monitoring Point I.D"
0
 
GravitaZ24Author Commented:
Yes, Thank you that was the problem! One of the Point ID's was identical. Thanks Everyone for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.