时间戳

首页 > 解决方案 > 合并来自多个 Application.GetOpenFilename 语句的结果

excel - 合并来自多个 Application.GetOpenFilename 语句的结果

问题描述

我的脚本使用 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

标签： excelvba

解决方案

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

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

推荐阅读