Vba Help: Object Required Error

Question

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

Answer 1

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.

Answer 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

Vba Help: Object Required Error

2 Answers

Must Read

Your Answer

Chloe Bennett

Vba Help: Object Required Error

2 Answers

Must Read

Your Answer

Sign up or log in

Post as a guest

Chloe Bennett