Store file names in global array variable

[Rudi]

Hi,

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

[HansV]

The code that you posted will fill the array variable varResult, and this will remain available until either the workbook is closed, or an unhandled error occurs in some VBA code (that will reset all variables).

If you want to store the list of files more permanently, you can copy it into a range of cells, for example in a hidden worksheet. Sample code:

Code: Select all

   Worksheets("HiddenSheet").Range("A:A").ClearContents
   Worksheets("HiddenSheet").Range("A1").Resize(RowSize:=UBound(varResult)).Value = _
      Application.Transpose(varResult)

[Rudi]

How about that...excellent!
TX for confirming.

[Rudi]

OK, I have a subscript out of range?
Seems the file names are not passing through?
Also, I selected 3 files, but the value i is equal to 4?

SP2-Sat,12-10.jpg

[Rudi]

Ah....
I used the Locals Window which cleared up the variable issue...

Code: Select all

Option Explicit
Public varResult As Variant, i As Integer, sActWB As String

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
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
End Sub

Sub Test2()
   Workbooks.Open Filename:=varResult(3), UpdateLinks:=False
   sActWB = ActiveWorkbook.Name
   'Do stuff in WB(3)
   Workbooks(sActWB).Close False
End Sub

[HansV]

After the For i ... Next i loop, i will be one more than the highest loop index, so it makes no sense to use it "as is" - it doesn't correspond to an array element any more.

[Jan Karel Pieterse]

If you are planning to use i as a loop counter in different routines, best NOT to declare it in the dclaration section of the module, but rather in each sub in turn. That keeps them separate.

[Rudi]

If you are planning to use i as a loop counter in different routines, best NOT to declare it in the dclaration section of the module, but rather in each sub in turn. That keeps them separate.

Noted.
TX for the advice.