宏将csv文件导入excel非活动工作表 [英] macro to Import csv file into an excel non active worksheet

问题描述

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

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.

问题1：我分配给按钮的vba导致csv文件的内容被放置在与按钮（面板工作表）相同的工作表上。我想将csv文件的内容放在数据表上。

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.

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

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?

任何帮助，以解决这个问题将非常感谢。分配给按钮的宏如下：

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$1"))
.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

推荐答案

这是你想要的吗？

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$1"))
        .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

这篇关于宏将csv文件导入excel非活动工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！