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

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
855
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
    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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.

VBA Code:
'   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
 
Upvote 0
No wonder nobody commented...the code is fine. I mistakenly DIMd the workbook object as a worksheet. Something basic indeed!
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top