VBA Command button to pull from Word Table

[JaredMcCullough]

I was wondering if it was plausible to have a VBA which takes data from a word table and plugs it in columns in excel. I have a standard word template that has two columns which get populated by a consultant and then sent to me. Looking for a VBA that would extract these columns and put them in my excel database. Is this possible?​

 

[tlowry]

Here's a start...

Sub getwordtable(wdFileName, exTargetCell)
    Dim irow, icol
    Dim wdDoc: Set wdDoc = GetObject(wdFileName)
    Dim tableNo: tableNo = wdDoc.tables.Count
    Select Case tableNo
        Case 0
            MsgBox "There are no tables in: " & wdFileName
        Case 1
        Case Else
           tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
                        "Enter table number of table to import", "Import Word Table", "1")
    End Select
    With wdDoc.tables(tableNo)
        For irow = 1 To .Rows.Count
            For icol = 1 To .Columns.Count
                exTargetCell.Cells(irow, icol) = _
                        Application.WorksheetFunction.Clean(.Cell(irow, icol))
            Next icol
        Next irow
    End With
    Set wdDoc = Nothing
End Sub
Sub test01()
    getwordtable "C:\temp\WordTable1.doc", Sheets(1).Range("A1")
End Sub

 

[JaredMcCullough]

Here's a start...

Sub getwordtable(wdFileName, exTargetCell)
    Dim irow, icol
    Dim wdDoc: Set wdDoc = GetObject(wdFileName)
    Dim tableNo: tableNo = wdDoc.tables.Count
    Select Case tableNo
        Case 0
            MsgBox "There are no tables in: " & wdFileName
        Case 1
        Case Else
           tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _
                        "Enter table number of table to import", "Import Word Table", "1")
    End Select
    With wdDoc.tables(tableNo)
        For irow = 1 To .Rows.Count
            For icol = 1 To .Columns.Count
                exTargetCell.Cells(irow, icol) = _
                        Application.WorksheetFunction.Clean(.Cell(irow, icol))
            Next icol
        Next irow
    End With
    Set wdDoc = Nothing
End Sub
Sub test01()
    getwordtable "C:\temp\WordTable1.doc", Sheets(1).Range("A1")
End Sub

Thank you for the reply.....I took a brief look at the code and was wondering if you could maybe briefly explain how its operating? I see its looking for a word file then through the word file for tables. I tried using this in a blank exel workbook but could not get it to function correctly

 

[tlowry]

The macro getwordtable reads a table from the word file specified in the first parameter (C:\temp\WordTable1.doc), and copies it into the open excel file at location specified by the second parameter (Sheets(1).Range("A1")). That is the command:

getwordtable "C:\temp\WordTable1.doc", Sheets(1).Range("A1")

will copy a table from C:\temp\WordTable1.doc into Sheets(1).Range("A1") -- cell in the upper left hand corner of the range.

Instructions:

1. Create, save, and close a word document with a table in it. (The full path and name will be the first parameter.)
2. Open a new excel document
3. Put the following code into a module (Alt F11, Insert, Module) copy and paste code
4. Modify the first parameter to point to your word document
5. Save the Excel document.
6. Run the macro Test01

[SIZE=1][FONT='inherit']Sub getwordtable(wdFileName, exTargetCell)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    Dim irow, icol[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    Dim wdDoc: Set wdDoc = GetObject(wdFileName)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    Dim tableNo: tableNo = wdDoc.tables.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    Select Case tableNo[/FONT][/SIZE]
[SIZE=1][FONT='inherit']        Case 0[/FONT][/SIZE]
[SIZE=1][FONT='inherit']            MsgBox "There are no tables in: " & wdFileName[/FONT][/SIZE]
[SIZE=1][FONT='inherit']        Case 1[/FONT][/SIZE]
[SIZE=1][FONT='inherit']        Case Else[/FONT][/SIZE]
[SIZE=1][FONT='inherit']           tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _[/FONT][/SIZE]
[SIZE=1][FONT='inherit']                        "Enter table number of table to import", "Import Word Table", "1")[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    End Select[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    With wdDoc.tables(tableNo)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']        For irow = 1 To .Rows.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']            For icol = 1 To .Columns.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']                exTargetCell.Cells(irow, icol) = _[/FONT][/SIZE]
[SIZE=1][FONT='inherit']                        Application.WorksheetFunction.Clean(.Cell(irow, icol))[/FONT][/SIZE]
[SIZE=1][FONT='inherit']            Next icol[/FONT][/SIZE]
[SIZE=1][FONT='inherit']        Next irow[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    End With[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    Set wdDoc = Nothing[/FONT][/SIZE]
[SIZE=1][FONT='inherit']End Sub[/FONT][/SIZE]
[SIZE=1][FONT='inherit']Sub test01()[/FONT][/SIZE]
[SIZE=1][FONT='inherit']    getwordtable "[B][COLOR=#008000]C:\temp\WordTable1.doc[/COLOR][/B]", Sheets(1).Range("A1")[/FONT][/SIZE]
[SIZE=1][FONT='inherit']End Sub[/FONT][/SIZE]

 

[JaredMcCullough]

tlowry,

thank you for the explanation....after looking at this i see you identify the location where the data from the word table will be extracted. Is this something that could be adjusted to be more of a dependent statement based upon say some sort of criterion? Ex. I have a word table that I want extracted into row 2 column B of worksheet 2 or something to that sort?

 

[JaredMcCullough]

The macro getwordtable reads a table from the word file specified in the first parameter (C:\temp\WordTable1.doc), and copies it into the open excel file at location specified by the second parameter (Sheets(1).Range("A1")). That is the command:

getwordtable "C:\temp\WordTable1.doc", Sheets(1).Range("A1")

will copy a table from C:\temp\WordTable1.doc into Sheets(1).Range("A1") -- cell in the upper left hand corner of the range.

Instructions:

1. Create, save, and close a word document with a table in it. (The full path and name will be the first parameter.)
2. Open a new excel document
3. Put the following code into a module (Alt F11, Insert, Module) copy and paste code
4. Modify the first parameter to point to your word document
5. Save the Excel document.
6. Run the macro Test01

[SIZE=1][FONT='inherit']Sub getwordtable(wdFileName, exTargetCell)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   Dim irow, icol[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   Dim wdDoc: Set wdDoc = GetObject(wdFileName)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   Dim tableNo: tableNo = wdDoc.tables.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   Select Case tableNo[/FONT][/SIZE]
[SIZE=1][FONT='inherit']       Case 0[/FONT][/SIZE]
[SIZE=1][FONT='inherit']           MsgBox "There are no tables in: " & wdFileName[/FONT][/SIZE]
[SIZE=1][FONT='inherit']       Case 1[/FONT][/SIZE]
[SIZE=1][FONT='inherit']       Case Else[/FONT][/SIZE]
[SIZE=1][FONT='inherit']          tableNo = InputBox("This Word document contains " & tableNo & " tables." & vbCrLf & _[/FONT][/SIZE]
[SIZE=1][FONT='inherit']                       "Enter table number of table to import", "Import Word Table", "1")[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   End Select[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   With wdDoc.tables(tableNo)[/FONT][/SIZE]
[SIZE=1][FONT='inherit']       For irow = 1 To .Rows.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']           For icol = 1 To .Columns.Count[/FONT][/SIZE]
[SIZE=1][FONT='inherit']               exTargetCell.Cells(irow, icol) = _[/FONT][/SIZE]
[SIZE=1][FONT='inherit']                       Application.WorksheetFunction.Clean(.Cell(irow, icol))[/FONT][/SIZE]
[SIZE=1][FONT='inherit']           Next icol[/FONT][/SIZE]
[SIZE=1][FONT='inherit']       Next irow[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   End With[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   Set wdDoc = Nothing[/FONT][/SIZE]
[SIZE=1][FONT='inherit']End Sub[/FONT][/SIZE]
[SIZE=1][FONT='inherit']Sub test01()[/FONT][/SIZE]
[SIZE=1][FONT='inherit']   getwordtable "[B][COLOR=#008000]C:\temp\WordTable1.doc[/COLOR][/B]", Sheets(1).Range("A1")[/FONT][/SIZE]
[SIZE=1][FONT='inherit']End Sub[/FONT][/SIZE]

tlowry,

thank you for the explanation....after looking at this i see you identify the location where the data from the word table will be extracted. Is this something that could be adjusted to be more of a dependent statement based upon say some sort of criterion? Ex. I have a word table that I want extracted into row 2 column B of worksheet 2 or something to that sort?​

 

[tlowry]

The second parameter indicates where the table will "go".

for

row 2 column B of worksheet 2

change the line to:

[FONT='inherit']]getwordtable "C:\temp\WordTable1.doc", [B][COLOR=#008000]Sheets(2).Range("B2")[/COLOR][/B][/FONT]

 

[JaredMcCullough]

The second parameter indicates where the table will "go".

for

change the line to:

[FONT='inherit']]getwordtable "C:\temp\WordTable1.doc", [B][COLOR=#008000]Sheets(2).Range("B2")[/COLOR][/B][/FONT]

Again thank you for the help. As I am gaining a little more understanding of how the macro works I seem to be under the understanding that it is taking the first table identified out of the word document. Is there a way to identify which table needs to be removed. I have a similar thread http://www.mrexcel.com/forum/excel-...lications-command-button-pull-word-table.html

where the individual provided a macro which allows you to search for the document and chose which table you are loading in. After reviewing the one you provided I saw similarities and was wondering if it was possible to incorporate the same idea.

 

[tlowry]

If there is more than one table in the document, the macro asks for the table number you wish to copy. Did you try this? Is there something different that you wanted?

 

[JaredMcCullough]

If there is more than one table in the document, the macro asks for the table number you wish to copy. Did you try this? Is there something different that you wanted?

Tlowry,

Sorry for the delayed response I was off all last week. The answer to your question is yes there is more than 1 table. The answer to did I try the macro is yes but it came up with an error code when I tried to run it.......I Chose the file and placed it in the location you had indicated but when I tried to run the test it came up with an error code. I was not certain if this it needed to be a C: Drive only file or if it could be from any drive type? Similarly the code in which I provided from the other individual allowed one to browse for files where as the macro which you provided would require you to go in and manually enter the address of the file. I was wondering if there was a way to combine the two........either or essentially what I am trying to do is take the table and direct it to a certain position within the excel workbook.