vba 将csv文件导入excel非活动工作表的宏

macro to Import csv file into an excel non active worksheet

提问by George

I have a macro enabled excel workbook that contains several named worksheets. One of the worksheets is named "panel" and a second worksheet is named "data". The sheet named "panel" has a button to which a macro is assigned. I would like to select the button on the worksheet named "panel" and have a browse for file window appear. Once the user selects the csv file on their hard drive, I would like the contents of the csv file to be imported into the worksheet named "data" starting in cell A1.

我有一个启用宏的 excel 工作簿，其中包含多个命名工作表。其中一个工作表命名为“面板”，第二个工作表命名为“数据”。名为“面板”的工作表有一个分配了宏的按钮。我想选择名为“面板”的工作表上的按钮并显示浏览文件窗口。一旦用户在其硬盘驱动器上选择了 csv 文件，我希望将 csv 文件的内容导入到从单元格 A1 开始的名为“数据”的工作表中。

PROBLEM 1: The vba I have assigned to the button causes the contents of the csv file to be placed on the same worksheet as the button (the "panel" worksheet). I would like the contents of the csv file to be placed on the "data" sheet.

问题 1：我分配给按钮的 vba 导致 csv 文件的内容与按钮（“面板”工作表）放在同一个工作表上。我希望将 csv 文件的内容放在“数据”表上。

PROBLEM 2: Also, there is a string of code referencing my hard drive and a file called "capture.csv". So when macro enabled excel file is on another computer, the file crashes. Any way to remove the pathway string so any computer could use the file?

问题 2：此外，有一串代码引用了我的硬盘驱动器和一个名为“capture.csv”的文件。因此，当启用宏的 excel 文件在另一台计算机上时，文件会崩溃。有什么方法可以删除路径字符串以便任何计算机都可以使用该文件？

Any assistance to fix this issue would be greatly appreciated. The macro assigned to the button follows:

任何解决此问题的帮助将不胜感激。分配给按钮的宏如下：

Sub load_csv()
Dim fStr As String
With Application.FileDialog(msoFileDialogFilePicker)
.Show
If .SelectedItems.Count = 0 Then
MsgBox "Cancel Selected"
End
End If
'fStr is the file path and name of the file you selected.
fStr = .SelectedItems(1)
End With
Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\laptop\Desktop\CAPTURE.csv", Destination:=Range("$A"))
.Name = "CAPTURE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
MsgBox fStr
End With
End Sub

回答by Siddharth Rout

Is this what you are trying?

这是你正在尝试的吗？

Sub load_csv()
    Dim fStr As String

    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        If .SelectedItems.Count = 0 Then
            MsgBox "Cancel Selected"
            Exit Sub
        End If
        'fStr is the file path and name of the file you selected.
        fStr = .SelectedItems(1)
    End With

    With ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=Range("$A"))
        .Name = "CAPTURE"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False

    End With
End Sub

回答by user2947053

For Excel on Mac, it seems the QueryTable object does not support the properties "PreserveFormatting" and "RefreshPeriod" and will give you a runtime error if you try and set them.

对于 Mac 上的 Excel，似乎 QueryTable 对象不支持属性“PreserveFormatting”和“RefreshPeriod”，如果您尝试设置它们，则会出现运行时错误。

Also, Application.FileDialog does not work with Mac either, but that is covered in other posts.

此外，Application.FileDialog 也不适用于 Mac，但这在其他帖子中有所介绍。

For Mac:

对于 Mac：

Sub load_csv()
Dim fStr As String

fStr = "Macintosh HD:Users:anthony:Documents:example.csv" 'Keeping file String simple for example.

With ThisWorkbook.Sheets("Data").QueryTables.Add(Connection:= _
"TEXT;" & fStr, Destination:=Range("$A"))
    .Name = "CAPTURE"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    '.PreserveFormatting = True  **commented out for Mac
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    '.RefreshPeriod = 0  **commented out for Mac
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = True
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False

End With
End Sub