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