Need to open all .xlsm files in folder, but not at the same time, automatically.

[9tanstaafl9]

I have 50+ workbooks, located in a single folder. Within that folder they are in subfolders (only one level deep). Each workbook has a macro set to run when the workbook is opened and it is after 11pm, then it saves and closes the workbook. What I need to have happen is a way to get the workbooks to open, one at a time. They are huge, so they need to open one at a time or it would crash their system. Is there ANY way, via VBA or some other form of automation to make this happen?

I would just set the workbooks to run at a particular time and set up something to make Excel open, but I don't think that will work, because I have no way to change the time that each workbook would run. They will all basically be copies of each other, and I have no control over the macros once I turn over the first one. I could set it up to open a master workbook that could trigger the others, I'm not sure of any way to make that work when I don't know the file names (end users will be copying the original file to make new versions).

Any advice would be appreciated. If you are a guru and know I'm just trying for something impossible, please let me know that as well so I can quit wasting my time trying to figure this out.

Also, if it's possible but the subfolders are a holdup, I could possibly make them just use a naming convention that would work instead, but I only want to do that as a last resort. Note the end users have ZERO macro experience and limited Excel knowledge so having them modify anything other than the file name isn't a possible solution.

Thank you in advance!

 

[Derek Brown]

The following code (tested with Excel 2016) will extract a list of files from a folder and/or sub-folder:

Option Explicit
Const cstrModule = "modFunctions"
Private strFiles() As String
Private lngFileKount As Long
Function GetFilesInFolder(strSourceFolderName As String, blIncludeSubfolders As Boolean) As Variant
    Erase strFiles
    lngFileKount = 0
    ListFilesInFolder strSourceFolderName, blIncludeSubfolders
    GetFilesInFolder = strFiles
End Function
Function ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' Requires reference to Microsoft Scripting Runtime
' Returns an array of filenames
Const cstrProcedure = "ListFilesInFolder"
Dim oFSO As Scripting.FileSystemObject
Dim oSourceFolder As Scripting.folder
Dim oSubFolder As Scripting.folder
Dim oFileItem As Scripting.File
    On Error GoTo HandleError
    Set oFSO = New Scripting.FileSystemObject
    On Error GoTo noSource
    Set oSourceFolder = oFSO.GetFolder(SourceFolderName)
    On Error GoTo HandleError
    For Each oFileItem In oSourceFolder.Files
        ReDim Preserve strFiles(lngFileKount)
        strFiles(lngFileKount) = oFileItem.Path
        lngFileKount = lngFileKount + 1
    Next oFileItem
    If IncludeSubfolders Then
        For Each oSubFolder In oSourceFolder.SubFolders
            ListFilesInFolder oSubFolder.Path, True
        Next oSubFolder
    End If
HandleExit:
    On Error Resume Next
    Set oFileItem = Nothing
    Set oSubFolder = Nothing
    Set oSourceFolder = Nothing
    Set oFSO = Nothing
    '
    Exit Function
    '
noSource:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & _
        "when attempting to access named directory"
    '
    '
    'Add action code here
    '
    '
HandleError:
    'ErrorHandle Err, Erl(), cstrModule & "." & cstrProcedure
    '
    '
    'Add action code here
    '
    '
    Resume HandleExit
End Function
Sub Test()
Dim i As Long
Dim myArray() As String
    'myArray = GetFilesInFolder("C:\TEST", False)
    myArray = GetFilesInFolder("C:\TEST", True)
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print i & " = " & myArray(i)
    Next i
    Erase strFiles
End Sub

"Each workbook has a macro set to run when the workbook is opened and it is after 11pm, then it saves and closes the workbook." - this action can then be done by a "Master" workbook that contains code to loop through each file and take appropriate action - you just need the filenames in the returned array.
See the "Test" VBA in the above code - just change as required.
Use "View/Immediate Window" to see the output from the VBA Print command.
Hope this helps.

 

[9tanstaafl9]

Derek,

First of all, THANK YOU! I think this is exactly what I was looking for even though I didn't know how to ask.

Second, I'm OK, but NOT GREAT with VBA and I'm not entirely sure I understand what parts I'm supposed to modify to get this to work. Is this the line I would change to get it to open each workbook?

Debug.Print i & " = " & myArray(i)

If not, which line would it be?

Secondly, would this result in multiple workbooks being open at once? That would definitely crash their system. The workbooks are running a macro that refreshes a large amount of SQL data, so each needs time to process and close before the next one opens. If so, I imagine I could add a five minute (or whatever) delay before going to the next i?

Again, I am so thankful for your help. That looks like a lot of work and thought for someone you don't know.

 

[Derek Brown]

I will try to help the best that I can but please be aware that I have an illness that makes it difficult to find much time writing VBA code and I do have many other things to do.
However, to start you off a bit more:
The code shown above was used to open and process more than 3,000 files in a folder one at a time, but you will have to make sure that the refreshing of the SQL data is NOT done "in the background". You may want to research that in the meantime.
To know that the process has finished, you may have to consider some "communication" method with the "Master" workbook - perhaps the creation of a text file on the C: drive using a known filename.
See my next post for a bit of code that you can add.

 

[9tanstaafl9]

Wow. I won't have nearly that many files. I can figure out the background stuff, please don't worry about it. You answered the most important question already, the rest is just details. Your time is valuable and I appreciate what you've already given me. Thank you.

 

[Derek Brown]

More code and info:

Dim strFilearray() As String
' Add other required variablea here
Erase strFilearray
strFilearray = GetFilesInFolder(ADD FOLDER NAME HERE, True)
For lngArrayKount = LBound(strFilearray) To UBound(strFilearray)
      If Right(strFilearray(lngArrayKount), 5) = ".xlsm" Then
           strWorkbook = strFilearray(lngArrayKount)
	   ' Open and process workbook here
      else
' Add code here if necessary
      End If
Next lngArrayKount

By the way, you will need to make sure that each workbook is closed in a tidy manner (all object resources/variables must be released/erased) otherwise the workbook may remain open in the background.