Macro execute to loop all word doc files in a folder

[hrh_dash]

How do i amend the code below so that the macro could select all word doc files in the folder?

There are no errors but when the code is executed line by line, the macro did not run through the Do While loop.

    Dim path        As String
    Dim oDoc        As Object
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile"
    
    myFile = Dir(path & "*.*")
    
    Do While myFile <> ""
        
        Set oDoc = documents.Open(filename:=path & myFile)
        
        Call extractdatafromwordtoexcel
        Call findcopyandpaste
        
        wordDoc.Close
        myFile = Dir
        
    Loop
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Appreciate the assist in advance..

[#2]

Is "Bizfile" the name of a folder, or is it the first part of the filename(s)? If it's a folder, then you need a "\" to separate "Bizfile" and "*.*".

C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile\*.*

[#3 · Accepted solution]

@hrh_dash 

Also, to be on the safe side, I'd explicitly loop through Word documents, so that you don't get an error if the folder contains another type of file:

    path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile\"
    
    myFile = Dir(path & "*.doc*")

[#4]

@Hans Vogelaar , 

, if i would like to add an additional file type such as docx; would the following be correct?

currently, nothing is being populated when the code is executed.

    myFile = Dir(path & "*.docx*;*.doc*")

[#5]

@hrh_dash 

"*.doc*" includes .doc, .docx and .docm

If the code doesn't process any documents, there must be something wrong with the path.

As @JMB17 asked: "Is "Bizfile" the name of a folder, or is it the first part of the filename(s)?"

[#6]

@Hans Vogelaar 

alright, i thought i would need to include "doc" and "docx" file type to have the code running. ok, so right now, the folder has 2 word document files.

Currently when the macro is executed,  data from the 1st word document file was populated twice rather then both word documents file being populated. 

Attaching the screenshot below:

Data in the 1st and 2nd column has been removed due to confidentiality.

@JMB17 , appreciate the response. Bizfile is folder. 

The macro works after inputting the "\" after the Bizfile and include the "*.docx*" but somehow it resulted in duplicate entries.

[#7]

@hrh_dash 

We don't know the code that is being called, but I notice that you open a document using oDoc as variable, but then close a document using a different variable wordDoc. Could that be the cause of the problem?

[#8]

@hrh_dash 

As Hans already stated, we don't know what's in the other procedures being called that extract the information. Specifically, we can't see how those procedures reference the word document referenced by the variable "oDoc" (because oDoc is a local variable and it's not being passed as an argument to the other procedures, so it's unclear how those procedures are referencing the document).

I'm assuming the word object variable itself is globally scoped - are the other procedures referencing the document by index number (documents(1))? If the first document is not closed (because the variable name "wordDoc" appears to be incorrect as Hans noted), then documents(1) would still be referencing the first document.

Personally, I would pass the document as an argument to the other function(s) instead of assuming which document it should process by some other means such as index number.

Macro1()

    Macro2 oDoc

End Sub

Macro2(byref localVariableName as object)

     debug.print localVariableName.Paragraphs.Count

End Sub

Also, you should consider adding Option Explicit to the top of your module, if you're not already using it. Then, the compiler will help catch variable name issues such as misspellings or if you accidentally used "wordDoc" when you meant "oDoc" (if worddoc is not module or global level).

[#9]

@JMB17 , @Hans Vogelaar ,

i think i know what was the issue. The 2 sub (extractdatafromwordtoexcel and findcopyandpaste) was taking reference from a range hence explains why there seems to be a duplicate data.

The macro works when i debug.print myFile.

However, i need to tweak the code for extracting data in a tables from a doc type document. Therefore, i believe the GetObject liner is throwing out issues. Is there a way to work around for this GetObject liner?

Dim path            As String
Dim oDoc            As Variant
Dim wordDoc         As Object

Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next

path = "C:\Users\hrhquek\Desktop\DEBT RECOVERY\Bizfile\"

myFile = Dir(path & "*.doc*")

Do While myFile <> ""
    Debug.Print myFile
    Set oDoc = documents.Open(filename:=path & myFile)
    
    Set wordDoc = GetObject(oDoc) '<-- I believe this is the error populating. Not able to take reference from a word document
    
    With wordDoc
        
        tbls = wordDoc.Tables.Count
        
        If tbls = 0 Then
            
            MsgBox "There no tables To extract"
            
        End If
        
        rowO = 6        'Extracting into cell A6
        
        For tbBegin = 1 To tbls
            
            With .Tables(tbBegin)
                
                For RowNo = 1 To .Rows.Count
                    For ColNo = 1 To .Columns.Count
                        
                        ws.Cells(rowO, ColNo) = Application.WorksheetFunction.Clean(.Cell(RowNo, ColNo).Range.Text)
                        
                    Next ColNo
                    
                    rowO = rowO + 1
                    
                Next RowNo
                
            End With
            
            rowO = rowO
            
        Next tbBegin
        
    End With
    
    Call findcopyandpaste
    
    'oDoc.Close
    myFile = Dir
    
Loop

Application.EnableEvents = True
Application.ScreenUpdating = True

[#10]

@hrh_dash 

I'm not sure what the purpose of wordDoc is since you already have a reference to the document with oDoc?

GetObject accepts string argument(s), but you're passing it an object. Since error handling is set to 'resume next' you're not getting an error. If you want wordDoc to also reference the document (but is this really necessary - do you really need 2 references to the same object?), then

Set wordDoc = oDoc

[#11]

@hrh_dash 

@JMB17 already explained that you don't need GetObject and the wordDoc variable. Get rid of it, it only confuses things. Use oDoc instead.

[#12]

@Hans Vogelaar , @JMB17 

alright, managed to get the code working. thanks for the assistance!