使用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)

希望这能有所帮助。