Load data from Word into Excel

[Robert Crandal]

My Excel VBA program uses the msoFileDialogFilePicker
form that lets the user choose a Word document.

The available Word documents to choose from are formatted
as follows: There is one line of text at the top of the document.
Next, right below the top header line, there is one table that
contains 6 columns and N rows.

I would like my Excel VBA program to scan each row of
the table in the Word document, then copy each row of data
into my Excel sheet.

Can anyone show me a basic example of how to do this?
Im not exactly sure how to reference a table object in a Word
document, then paste data back into an Excel file.

I'd appreciate any help. Thank you!

 

[Claus Busch]

Hi Robert,

Am Wed, 1 May 2013 03:18:00 -0700 schrieb Robert Crandal:

The available Word documents to choose from are formatted
as follows: There is one line of text at the top of the document.
Next, right below the top header line, there is one table that
contains 6 columns and N rows.

following code works for me. But I think for you the delimiter for
TextToColumns is comma. Have a try:

Sub ImportFromWord()
Dim objWord As Object
Dim myPath As String

Set objWord = CreateObject("Word.Application")
myPath = "C:\Users\Claus Busch\Desktop\Test.docx"

With objWord
.documents.Open myPath
.Visible = True
.Activate
.Selection.wholestory
.Selection.Copy
End With

Application.Goto ThisWorkbook.Sheets("Sheet1").Range("A1")
ActiveSheet.Paste

On Error Resume Next
Selection.TextToColumns _
semicolon:=True, _
fieldinfo:=Array(Array(1, 1), _
Array(2, 1), _
Array(3, 1), _
Array(4, 1), _
Array(5, 1), _
Array(6, 1))
Rows(1).Delete
objWord.Application.Quit
End Sub


Regards
Claus Busch

 

[Robert Crandal]

following code works for me. But I think for you the delimiter for
TextToColumns is comma. Have a try:

That code is a good start. I didn't really want to
copy the entire table with all the table colors
and thick borders too, but it worked.

Rather than copying the entire table data plus
formatting, is it possible to access the table
object using the VBA object model? I
would like to use Excel to only extract the
data from the Word table object and paste
it into Word.

 

[Claus Busch]

Hi Robert,

Am Wed, 1 May 2013 12:02:53 -0700 schrieb Robert Crandal:

That code is a good start. I didn't really want to
copy the entire table with all the table colors
and thick borders too, but it worked.

IMO is that the easiest and quickest way to import the data from a
table. You can delete formats and borders after the import.


Regards
Claus Busch

 

[Robert Crandal]

IMO is that the easiest and quickest way to import the data from a
table. You can delete formats and borders after the import.

Hi Claus. I was finally able to create a solution. In case you're
curious, here's what it looks like. I tweaked your code
slightly:

'------------------------------------------------------------------
Sub ImportFromWord()

Dim objWord As Object
Dim oTable As Table
Dim oRow As Row
Dim r As Integer

' Load target Word document
Set objWord = CreateObject("Word.Application")
objWord.documents.Open ("C:\word_data.doc")
objWord.Visible = True
objWord.Activate

Set oTable = objWord.ActiveDocument.Tables(1)

' Go through all rows of Word table
For r = 1 To oTable.Rows.Count
Set oRow = oTable.Rows(r)

' Paste data back into Excel. This Word table has 6 columns
Sheet1.Cells(r, 1).Value = oRow.Cells(1).Range.Text
Sheet1.Cells(r, 2).Value = oRow.Cells(2).Range.Text
Sheet1.Cells(r, 3).Value = oRow.Cells(3).Range.Text
Sheet1.Cells(r, 4).Value = oRow.Cells(4).Range.Text
Sheet1.Cells(r, 5).Value = oRow.Cells(5).Range.Text
Sheet1.Cells(r, 6).Value = oRow.Cells(6).Range.Text
Next r

' Quit Word
objWord.Application.Quit

End Sub
'----------------------------------------------------------------

The data pasted from each cell of the Word table contains
two weird whitespace or bullet characters at the end, but I'm
sure I can trim off the two characters.

Robert

 

[Claus Busch]

Hi Robert,

Am Thu, 2 May 2013 11:43:36 -0700 schrieb Robert Crandal:

Hi Claus. I was finally able to create a solution. In case you're
curious, here's what it looks like. I tweaked your code
slightly:

I tested both solutions. At the end of my code I added:
ActiveSheet.UsedRange.ClearFormats
to clear the formats and the borders.
With 6 columns and 5 rows, both solutions needed the same time, 1.633
sec.
But with 15 rows, your code needed 2.281 and mine 1.941.
With more rows my solution will be faster.


Regards
Claus Busch

 

[Robert Crandal]

For r = 1 To 6
Columns(r).TextToColumns Destination:=Cells(1, r), _
DataType:=xlDelimited, Tab:=True, FieldInfo:=Array(1, 1)
Next

Hi Claus.... I tried pasting the above code into the code
that I tweaked but it didnt work. The error message that
I got was "No data was selected to parse".

What does your final code look like for this entire
function?

Thanks.

 

[Claus Busch]

Hi Robert,

Am Thu, 2 May 2013 13:56:01 -0700 schrieb Robert Crandal:

What does your final code look like for this entire
function?

Sub ImportFromWord()

Dim objWord As Object
Dim oTable As Table
Dim oRow As Row
Dim r As Integer

' Load target Word document
Set objWord = CreateObject("Word.Application")
objWord.documents.Open ("C:\Users\Claus Busch\Desktop\Test.docx")
objWord.Visible = True
objWord.Activate

Set oTable = objWord.ActiveDocument.Tables(1)

' Go through all rows of Word table
For r = 1 To oTable.Rows.Count
Set oRow = oTable.Rows(r)

' Paste data back into Excel. This Word table has 6 columns
Sheet1.Cells(r, 1).Value = oRow.Cells(1).Range.Text
Sheet1.Cells(r, 2).Value = oRow.Cells(2).Range.Text
Sheet1.Cells(r, 3).Value = oRow.Cells(3).Range.Text
Sheet1.Cells(r, 4).Value = oRow.Cells(4).Range.Text
Sheet1.Cells(r, 5).Value = oRow.Cells(5).Range.Text
Sheet1.Cells(r, 6).Value = oRow.Cells(6).Range.Text
Next r

For r = 1 To 6
Columns(r).TextToColumns Destination:=Cells(1, r), _
DataType:=xlDelimited, Tab:=True, fieldinfo:=Array(1, 1)
Next

' Quit Word
objWord.Application.Quit
End Sub


Regards
Claus Busch