Looping files in folders, cannot set wb object to files to open

[OaklandJim]

I am trying to iterate though files in three folders to update the files. I thought that it would be straightforward. My code opens the first file in the first folder then crashes. I sure hope that you can assist.

Below is offending code with comments and below that is the debug.print output that I used to confirm that I am iterating through all the files (there are nine files, three in each of three folders.) Note that the folder names are in an array asFolders.

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    
'   Get the base path for all folders being processed.
    sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"

    For iFolder = 1 To iFoldersCount
        
        Set oFolder = oFSO.GetFolder(sBasePath & asFolders(iFolder))
        
        Set oFiles = oFolder.Files
        
'This shows the correct #.
'Debug.Print "oFiles.Count = " & oFiles.Count
        
        For Each oFile In oFiles

'This works, all file names are shown. Names are as expected.
'Debug.Print oFile.Name

'This shows all file names with path. Values printed are as expected.
Debug.Print sBasePath & asFolders(iFolder) & "\" & oFile.Name

'           This opens the first workbook in the first folder then, when trying to open the
'           next workbook it throws up a Type Mismatch error. All files are type .xlsx
            'Set wbTarget = Workbooks.Open(sBasePath & asFolders(iFolder) & "\" & oFile.Name)

'           Process the file
            With wbTarget
            
'This prints the name of the first file.
'Debug.Print wbTarget.Name

'               This crashes even on the first file. Error type: Method or Data Member Not Found
                'wbTarget.Close
                'Set wbTarget = Nothing
                
            End With

        Next oFile
    
    Next iFolder

C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other3.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic3.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel3.xlsx

 

[OaklandJim]

I tried an approach using Dir but that chokes after opening the first file, like the other approach. What am I missing? Debug.Print results are shown below code. It shows the list of files that I am trying to process. I quadruple checked that all files exist.

'   Get the base path for all folders being processed.
    sBasePath = Left(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\") - 1) & "\"

    For iFolder = 1 To iFoldersCount
       
        vFiles = Dir(sBasePath & asFolders(iFolder) & "\")
       
        While (vFiles <> "")

'This shows the correct list of files with path.
Debug.Print sBasePath & asFolders(iFolder) & "\" & vFiles
           
'           This crashes after opening the first file, Type Mismatch error.
            'Set wbTarget = Workbooks.Open(sBasePath & asFolders(iFolder) & "\" & vFiles)
            vFiles = Dir
        Wend
           
    Next iFolder

C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Other\Other3.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Plastic\Plastic3.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel1.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel2.xlsx
C:\Users\Jim\Desktop\Mr Excel\Machining\Steel\Steel3.xlsx

 

[OaklandJim]

No wonder nobody commented...the code is fine. I mistakenly DIMd the workbook object as a worksheet. Something basic indeed!

 

[smozgur]

No wonder nobody commented

I believe nobody saw the declaration line since it is not included in the code. That's why it is important to post the sub-procedures as a whole in such mysterious cases. Thanks for posting the solution.