VBA - looping coding gets error "can't find file" (where coding has error is not consistent)

[tuple1020]

Hi there,

Background story: I have coding that loops through .xls* files. It starts out at the named parent folder, which is organized then in subfolders(layer1), and those subfolders have subfolders(layer2), which contain the .xls* files. The subfolders(layer1), subfolders(layer2), and excel file names are referenced using variables when opening the .xls* files.
Problem 1: After about an 1hr, 1.5hrs, I get an error saying that VBA cannot find the file, but when I check the location, everything looks fine. I thought that because there are so many files, perhaps it was actually a memory issue. But if it was, then VBA would have told me so...... Maybe the coding takes too long to run and excel doesn't like it?...
Problem 1.2: The coding does not come up with the error at the same .xls* file. Sometimes it'll run fine, go past where it had stopped in a previous run, then stop at a later file.
The coding runs just fine for dozens of files, working with various subfolders.. So I don't think there's something wrong with how the coding is set up in terms of looping and locating files using variables.
If anyone has any thoughts, I would greatly appreciate it.
Thanks.

 

[PhilS2520]

Hello tuple1020

Without looking at the code it is hard to diagnose the problem. So let me say that if the code takes so long to process how many workbooks? I think a stack overflow maybe the issue, so here is what I want you to try.

set some watches for variables you use. Also you can use a break point so the code stops and gives you control, you know using an F9 for that and the line you choose to place the breakpoint at is important. Then you can step through the code using the F8 function key and watch each line execute. You can hover the mouse over a variable name and see what it contains at that time.

Also, try and do some clean up. If an object variable is no longer needed, get rid of it by setting it to nothing.

Do these, and report back.

Hope this helps.

 

[tuple1020]

Thank you for responding, Phil. I am new to posting in this forum. Is there a better way I can post coding without it taking up so much space?
I've pasted my coding below. "Folder 1", "Folder 2", and "Current Projects" are names I used to replace actual names for privacy reasons. I apologize I couldn't post this earlier, as it was saved on another computer.

-----------------------------------------------------------------

Private Sub name()

'then opens 2 spreadsheets that the other spreadsheets will look at when they are being updated. 
Dim fiscalyear As String
Dim strFolder As String
Dim strFolder2 As String
Dim strSubFolder As String
Dim strSubFolder2 As String
Dim strFile As String
Dim colSubFolders As New Collection
Dim colSubFolders2 As New Collection
Dim varItem As Variant
Dim varItem2 As Variant
Dim wbk As Workbook

fiscalyear = Workbooks("Master Macro File").Sheets(1).Range("J3").Value
'Parent folder including trailing backslash
strFolder = "M:\Folder 1\Folder 2" & fiscalyear & " Current Project"
'Loop through subfolders and fill Collection object
strSubFolder = Dir(strFolder & "*", vbDirectory)
Do While Not strSubFolder = ""
    Select Case strSubFolder
    Case ".", ".."
        'Current folder or parent folder - ignore
    Case Else
        'Add to collection
        colSubFolders.Add Item:=strSubFolder, Key:=strSubFolder
    End Select
    'On to the next one
    strSubFolder = Dir
Loop
'--------------------------------------------------------------------------------------------
'Getting coding ready to recognize subfolders(layer2) within subfolders(layer1) (which are then in the parent folder) 
For Each varItem In colSubFolders
        'Parent folder including trailing backslash
        strFolder2 = "M:\Folder 1\Folder 2" & fiscalyear & " Current Project" & varItem & ""
        
        'Loop through the subfolders and fill Collection object
        
        strSubFolder2 = Dir(strFolder2 & "*", vbDirectory)
        Do While Not strSubFolder2 = ""
        
            Select Case strSubFolder2
                Case ".", ".."
                Case Else
                    colSubFolders2.Add Item:=strSubFolder2, Key:=strSubFolder2
            End Select
            strSubFolder2 = Dir
            
        Loop
        
Next varItem
'------------------------------------------------------------------------------------------
'Loop through the collection
For Each varItem In colSubFolders
    For Each varItem2 In colSubFolders2
    
    'Loop through Excel workbooks in subfolder
    
    strFile = Dir(strFolder & varItem & "" & varItem2 & "\*.xls*", vbDirectory)

    
    Do While strFile <> ""
        'Open workbook
        Set wbk = Workbooks.Open(Filename:=strFolder & varItem & "" & varItem2 & "" & strFile, AddToMRU:=False)

     
        
'*******************************************************************************************************************************
        'HERE is where you start putting in your own coding that you want repeated for each excel file in these subfolders.
'*******************************************************************************************************************************
 
'personal coding, just editing the documents. 

'*******************************************************************************************************************************
        'Above this line is where your custom coding will end. The next few lines below just make it look back to the next file, etc.
'*******************************************************************************************************************************
            
            strFile = Dir
            
            Loop
    Next varItem2
    
Next varItem

'then closes out other docs were we looking at, goes to the parent file, and ends the sub.

 

[Fluff]

Is there a better way I can post coding without it taking up so much space?

yes if you use code tags, which is the # icon in the reply window. Simply click the icon & than paste your code between them.

 

[tuple1020]

You are amazing, thank you!!