I would like to run a prompt to collect multiple files, and store it in a variable.
Then I have several other macros that must cycle through this file list and run their actions to process the files.
The person will want to run these several macros in different combinations, so when they run one it will run looping through the files list. Later, run a different macro and it will loop through the same files.
Ideally the files list will only be reset when they close the WB that contains the macros, or if they run the below macro again, to choose a different set of files.
So in essence, the below macro must store the files selected and other macros will refer to the variable and loop through the file list processing things in these files.
Will the macro below create the file list variable, and how do I store it "permanently" until the WB closes or it reruns?
TX
Code: Select all
Option Explicit
Public varResult As Variant, i As Integer
Sub OpenWBs()
varResult = Application.GetOpenFilename _
(FileFilter:="Excel Files, *.xls*", MultiSelect:=True)
If Not IsArray(varResult) Then
MsgBox "No file was selected.", vbExclamation
Exit Sub
End If
For i = LBound(varResult) To UBound(varResult)
Next i
End Sub
Sub Test()
For i = LBound(varResult) To UBound(varResult)
Application.StatusBar = "Processing file: " & i & " of " & UBound(varResult)
Workbooks.Open Filename:=varResult(i), UpdateLinks:=False
sActWB = ActiveWorkbook.Name
'Do stuff in WB
Workbooks(sActWB).Close True
Next i
Application.StatusBar = False
End Sub
Sub Test2()
Workbooks.Open Filename:=varResult(3), UpdateLinks:=False
sActWB = ActiveWorkbook.Name
'Do stuff in WB(3)
Workbooks(sActWB).Close True
End Sub