macro to Import csv file into an excel non active worksheet

Question

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.

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.

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

Solution

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$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

Comments

[George]

Thank you for taking the time to help me. I assigned the button the code you wrote. When I load the csv file, a message window appears: "Run-time error '-2147024809(80070057) The destination range is not on the same worksheet the query table is being created on."

[Siddharth Rout]

Change Destination:=Range("$A$1")) to Destination:=ThisWorkbook.Sheets("Data").Range("$A$1"))

[Admin]

For utf-8 encoding, change to .TextFilePlatform = -535

[Alg_D]

I found the answer to my question, by replacing RefreshStyle = xlInsertDeleteCells to .RefreshStyle = xlOverwriteCells

[user3504751]

any idea how to do place the file into a new sheet within the workbook ?