excel - 使用 VBA 将多个 CSV 文件导入 Excel 中的单个工作表(目前只能做 1 个)

Question

我设计了一个启用 VBA 的工作簿，允许用户选择一个 .csv 文件(这是从另一个系统导出的客户)，然后它基本上处理它以根据各种用户定义的标准等生成许多不同的用户群组.

这一切都非常好。但是，它一次只能处理一个 .csv 文件。

我目前采用的方法基本上是将所选 CSV 文件的内容导入到一个新工作表中，然后我只需询问该数据并使用它做我需要做的事情。但是，很长时间没有使用 VBA，我不确定如何在我已经编码的基础上进行构建，以允许选择多个 CSV 文件。

    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        'We only want to allow CSV files as this is what the ADT comes in
        .Filters.Add "ADT CSV Files", "*.csv", 1
        'Show the dialog box
        .Show

    'Error check in case user cancels dialog box to prevent type-mismatch error

    If (.SelectedItems.Count = 0) Then

        Range("C19").Value = "File selection aborted."
    Else
        'Store in fullpath variable
        Range("C19").Value = "Processing..."
        fullpath = .SelectedItems.Item(1)
    End If
    End With

    'A final check to make sure that the user hasn't done anything odd and somehow selected an invalid file format

    If InStr(fullpath, ".csv") = 0 Then
        Exit Sub
    End If

    Range("J26").Value = "Source File:"
    Range("J27").Value = fullpath

    'Now we grab the data from the file and import it into a new sheet within workbook

    Set Ws = ThisWorkbook.Sheets.Add
    Ws.Name = "ADT Data"

    'The ADT seems to be using fairly standard formatting conditions, so the following should surfice

         With Ws.QueryTables.Add(Connection:= _
        "TEXT;" & fullpath, Destination:=Ws.Range("$A$1"))

        .Name = "ADT Data"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileCommaDelimiter = True
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With

    'Now we trigger our main triage processes

    Call Extract

我假设我需要将选定的文件添加到数组中，然后循环遍历它们，但是根据我的 VBA 知识，我不确定如何实现。

Answer 1

您将此选项设置为 false

.AllowMultiSelect = False

我会创建一个对象来引用 FileDialog

'Declare a variable as a FileDialog object and set it like so:
Dim fd As FileDialog
'Create a FileDialog object as a File Picker dialog box.
Set fd = Application.FileDialog(msoFileDialogFilePicker)

然后，您可以遍历 fd 对象的 SelectedItems 集合。
For Each vrtSelectedItem In .SelectedItems

然后对选择的每个文件进行操作。

AllowMultiSelect 文档有一些很好的信息。

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa210129(v%3Doffice.11)

希望这可以帮助。