vba 宏以提示用户选择 CSV 文件以导入到工作簿中的现有工作表

Macro to prompt user to select CSV files for import into existing sheet in workbook

提问by AG10

I am running a macro which automatically takes csv files and import them into specific worksheets in my workbook. However, I would want to add greater flexibility by having the user select the files for import rather than have the macro automatically grab the csv files because the naming could change as well as the directory. I am new to VBA and have been trying to better understand the MsoFileDialogType and GetOpenFilename but having difficulty trying to grasp the concept/implementation into my code.

我正在运行一个宏，它会自动获取 csv 文件并将它们导入到我的工作簿中的特定工作表中。但是，我希望通过让用户选择要导入的文件而不是让宏自动获取 csv 文件来增加更大的灵活性，因为命名和目录可能会发生变化。我是 VBA 的新手，一直试图更好地理解 MsoFileDialogType 和 GetOpenFilename，但在尝试将概念/实现理解到我的代码中时遇到了困难。

What I ultimately want is for the user to click a button on the workbook front-end. Be prompted with a message to select the first csv file for import. This csv file will be imported into a pre-named worksheet in the workbook temp1. However since the data files come in pairs, I want the user to be able to select the next csv file after the first one into temp2.

我最终想要的是让用户单击工作簿前端的按钮。系统会提示一条消息，以选择要导入的第一个 csv 文件。此 csv 文件将导入到工作簿 temp1 中预先命名的工作表中。但是，由于数据文件是成对出现的，我希望用户能够在第一个 csv 文件之后选择下一个 csv 文件到 temp2。

What I have currently is:

我目前拥有的是：

Worksheets.Add
ActiveSheet.Name = "temp1"
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;MAC Directory path here" _
        , Destination:=Range("A1"))
        .Name = "temp 1 03.02.12"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
End With
ActiveSheet.Move after:=Worksheets(Worksheets.Count)

Thank you.

谢谢你。

回答by Fionnuala

Perhaps something on these lines.

也许在这些方面。

Sub GetCSVList()
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(msoFileDialogFilePicker)
With dlgOpen
    .AllowMultiSelect = True
    ''Start in
    .InitialFileName = "Z:\docs\"
    .Show
End With

For Each fname In dlgOpen.SelectedItems
    ImportCSV fname
Next
End Sub

Sub ImportCSV(fname)
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = "temp" & Worksheets.Count + 1

With ws.QueryTables.Add( _
        Connection:="TEXT;" & fname, _
        Destination:=Range("A1"))
    .Name = "Temp" & Worksheets.Count + 1
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = xlMacintosh
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .Refresh BackgroundQuery:=False
    '.UseListObject = False
End With
End Sub