I'm trying to take data from a bunch of word documents and add them to an excel spreadsheet using the below code I found on this site. However, after editing the code to select specific documents via the path, I continue to get "object required" errors when I try to run the code. Could you please explain where I'm going wrong and how to fix it? Sorry I'm fairly new to VBA and I'm pretty lost as a result.

 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:\Users\arahmani\Desktop\march\" '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 Dim t As ListObject For Each t In ActiveDocument.Tables For Each Row 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 xlRow = xlRow + 1 xlCol = 0 Next Row Next t ActiveWindow.Close False myFile = Dir Loop xl.Visible = True
End Sub

2

2 Answers

Difficult to be sure, but as a starting point

Dim t As ListObject

Should be

Dim t As Table

If this is Mac Office, you could well be getting a problem even when you try to do the initial CreateObject.

2

Object required (Error 424) occurs because:

References to properties and methods often require an explicit object qualifier.

You are trying to use MS Word document without declaring it. You should create the Word application object first.

Set wrd = CreateObject("word.Application")

Then use it to open the document.

wrd.Documents.Open Filename:=myPath & myFile

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy