CODEWITHSUNDEEP
vbaexcelcsv
Mario Reyes
Mario Reyes

Reputation: 3

Importing CSV to another worksheet

I need to import my data into another Excel sheet instead of active worksheet.

I have 2 sheets the UI and the DATA_List. My button is in the UI. I want to import the csv file to data_list sheet.

Sub btnImport_Click()

Dim slect As String
Set r = Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1)
With Application.FileDialog(msoFileDialogFilePicker)
    .Show
    If .SelectedItems.Count = 0 Then
        MsgBox "Cancel Selected"
        Exit Sub
    End If

    slect = .SelectedItems(1)
End With

With ThisWorkbook.Sheets("UI").QueryTables.Add(Connection:= _
"TEXT;" & slect, Destination:=r)
    .Name = "Data"
    .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 = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

Upvotes: 0

Views: 87

Answers (1)

Rob Anthony
Rob Anthony

Reputation: 1813

Change the first line to

Set r = Worksheets("data_list").Range("A1")

You will also need to change the line

With ThisWorkbook.Sheets("UI").QueryTables.Add(Connection:= _
"TEXT;" & slect, Destination:=r)

To

With ThisWorkbook.Sheets("data_list").QueryTables.Add(Connection:= _
"TEXT;" & slect, Destination:=r)

Upvotes: 1

Related Questions