Importing to Excel 2003 - Code from Jean-Yves

[PA]

I found the below code in this group, provided by Jean-Yves. The OP was
someone named Pepper.
It is to import a series of tables from MS Word files, starting with the
second row of the table, into Excel. I have a similar task to perform. I
tried this and was getting a Compile error at the second line,

Dim wdApp As Word.Application

The error is User-defined type not defined
I couldnt get past this.
Furthermore, there is a single line in each of the Word files, above the
tables, that I need to imported as well, and would be most suitable if this
line went into column A, to the left of the first imported row from each of
the Word
tables, which would start in Column B of the destination Excel file. I hope
I explained the adequately to be understood. The code follows:


Sub WordToExcel()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim x As Integer
Dim strFilename As String
Dim temp As String

Set wdApp = New Word.Application
'initialise counter
x = 1
'search for first file in directory
strFilename = Dir("C:/Temp/*.doc") 'amemd folder name
Do While strFilename <> ""
Set wdDoc = wdApp.Documents.Open(strFilename)
temp = wdDoc.Tables(1).Cell(2, 1).Range.Text 'read word cell
Range("A2").Offset(x, 0) = temp
temp = wdDoc.Tables(1).Cell(2, 2).Range.Text 'read word cell
Range("A2").Offset(x, 1) = temp
'etc


wdDoc.Close
x = x + 1
strFilename = Dir
Loop
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub

 

[Joel]

You are missing the Reference to Microsoft word in VBA

Tools - References then locate Microsoft Word Object and check box then
press OK. Chosse the latest version of the word object on your computer.

 

[PA]

Thanks for the tip, I am having my Desktop software group fix the Word Object
Library issue. It is missing. Large companies equals long waits for
seemingly easy to address issues.
In the meantime, if I have a line of text above my tables, in each file and
I would like to have it imported into Column A, for each word file, lined up
with the first row of the imported table starting in Column B, how would I
amend the code?
Thank you very, very much.

 

[Joel]

Try these changes

Sub WordToExcel()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim x As Integer
Dim strFilename As String
Dim temp As String
Dim MyRange As Word.Range

Set TSheet = ActiveWorkbook.ActiveSheet

Set wdApp = New Word.Application
'initialise counter
RowCount = 1
'search for first file in directory
strFilename = Dir("C:/Temp/*.doc") 'amemd folder name
Do While strFilename <> ""
Set wdDoc = wdApp.Documents.Open(strFilename)

With wdDoc
temp = .Tables(1).Cell(2, 1).Range.Text 'read word cell
TSheet.Range("B" & RowCount) = temp
temp = .Tables(1).Cell(2, 2).Range.Text 'read word cell
TSheet.Range("C" & RowCount) = temp

'etc
Set MyRange = .Tables(1).Range
With MyRange
.Start = .Start - 1
.Collapse wdCollapseStart
.Move wdSentence, -1
MySentence = .Sentences(1)
TSheet.Range("A" & RowCount) = MySentence
End With
End With

wdDoc.Close
RowCount = RowCount + 1
strFilename = Dir
Loop
wdApp.Quit
Set wdDoc = Nothing
Set wdApp = Nothing
End Sub