4
I know this question was already asked (Copying data from multiple word docs into one excel sheet) the thing is I can't use the answer.
I'm fresh to VBA, but I thought I can handle it. I was wrong. I was trying to use the code provided in the mentioned thread to parse some Word documents, at first with some amendments, then just using the original code. Unfortunately, I get the "object required" run-time error.
The code is provided below. The documents I'm trying to get data from are Word 2003 files (I first tried to change the "docx" to "doc", then to save the documents in docx and using the original script, didn't help). One thing is that they are in fact scanned and ocr'ed paper documents, so...
a) most of the tables inside are kept in frames (don't know if it changes anything, supposedly no, considering their xml structure)
b) when I try to save them as docx the application first proposes to save them as rtfs. So maybe they're in fact rtf files, not .doc?
Sub macro1()
Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Workbooks.Add
xl.Visible = True
'Here put your path where you have your documents to read:
myPath = "C:\some\path\" 'End with '\'
myFile = Dir(myPath & "*.docx")
xlRow = 1
Do While myFile <> ""
Documents.Open Filename:=myPath & myFile, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:=""
xlCol = 0
For Each t In ActiveDocument.Tables
For Each r In t.Rows
For Each c In r.Range.Cells
myText = c
myText = Replace(myText, Chr(13), "")
myText = Replace(myText, Chr(7), "")
xlCol = xlCol + 1
xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, xlCol) = myText
Next c
xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, xlCol + 1) = myFile
xlRow = xlRow + 1
xlCol = 0
Next r
Next t
ActiveWindow.Close False
myFile = Dir
Loop
xl.Visible = True
End Sub
Why can't you use the answer from http://superuser.com/questions/645165/copying-data-from-multiple-word-docs-into-one-excel-sheet?
– Dave – 2015-01-09T14:19:40.717