合并多个Application.GetOpenFilename语句的结果

Question

我的脚本使用Application.GetOpenFilename获取文件名列表，并将它们分配给变量声明变量(varList1(。到目前为止，剧本运行良好。

我想再次调用Application.GetOpenFilename以获得第二个文件名列表(varList2(。

如何将varList2附加到varList1，或者如何将两者组合到第三个变量(varList(？这将比编辑脚本以循环浏览第二个文件名列表更容易处理。

Dim varList1, varList2, varList As Variant
Dim lReturn As Long
Dim itm as Object
lReturn = SetCurrentDirectoryA(sBasePath & "" & lYear & "")
varList1 = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose Excel files to merge", MultiSelect:=True)
lReturn = SetCurrentDirectoryA(sBasePath & "" & lYear+1 & "")
varList2 = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose Excel files to merge", MultiSelect:=True)
varList = ?
or
(This give an "object required" error)
For each itm in varList2
varList1.Add(itm)
next itm

Answer 1

如果选择多个文件，则变量是一个数组。此代码创建第三个数组来存储所有文件。

Sub GetFiles()
Dim varList1, varList2, varList As Variant
Dim lReturn As Long
Dim itm As Variant

Dim FileList() As String  ' all files

lReturn = SetCurrentDirectory(sBasePath & "" & lYear & "")
varList1 = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose Excel files to merge", MultiSelect:=True)
lReturn = SetCurrentDirectoryA(sBasePath & "" & lYear + 1 & "")
varList2 = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Choose Excel files to merge", MultiSelect:=True)

If IsArray(varList1) Then  '<~~ If user selects multiple file
ReDim Preserve FileList(UBound(varList1) - 1) ' expand main array
For i = LBound(varList1) To UBound(varList1)  ' copy files
FileList(i - 1) = varList1(i)
Next i
Else                       ' <~~ If user selects single file
FileList(0) = varList1
End If

curcnt = UBound(FileList)  ' from first list

If IsArray(varList2) Then  '<~~ If user selects multiple file
ReDim Preserve FileList(UBound(FileList) + UBound(varList2))  ' expand main array
For i = LBound(varList2) To UBound(varList2)  ' add new files
FileList(i + curcnt) = varList2(i)
Next i
Else                       '<~~ If user selects single file
FileList(1 + curcnt) = varList2
End If

For Each itm In FileList
MsgBox itm
Next itm
End Sub