Importing data from .csv to excel document using VBA

Question

wondering if you can help out with a VBA issue. I pieced together the following without really knowing what I was doing:

Sub Import_Raw_Stripe_data()

    Dim fileDialog As fileDialog
    Dim strPathFile As String
    Dim strFileName As String
    Dim strPath As String
    Dim dialogTitle As String
    Dim Tworkbook As Workbook
    Dim Sworkbook As Workbook


dialogueTitle = "Select File to Import"
Set fileDialogue = Application.fileDialog(msoFileDialogFilePicker)
With fileDialogue
    .InitialFileName = "L:\Downloads"
    .AllowMultiSelect = False
    .Filters.Clear
    .Title = dialogueTitle

    If .Show = False Then
        MsgBox "No file selected."
        Exit Sub
    End If
    strPathFile = .SelectedItems(1)
End With

Set Sworkbook = Workbooks.Open(fileName:=strPathFile)
Set Tworkbook = ThisWorkbook



End Sub

Which, as far as I can tell opens a file dialog in excel, allows a user to choose a document and then opens it.

What I would like to do is the following:

1) Open a file dialogue and select a .csv file to import data from (complete?) into a .xlsm master file (with multiple sheets).

2) Select certain columns from the .csv (column A, Q, R and S in this case), copy them and import them into the second sheet of the master excel file entitled "Raw Stripe Data".

Any help in the matter would be greatly appreciated.

Update: I managed to find the following code:

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("Stripe Raw Data").QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=ThisWorkbook.Sheets("Stripe Raw Data").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
        ActiveWorkbook.Save



    End With

End Sub

This works great - but is there anyway to have it not override the data already imported? (for example, if i use it twice, the second import overrides the first).

Answer 1

ThisWorkbook.Sheets("Stripe Raw Data").Range("$A$1") specifies where the imported data is written to, that is the first cell of the sheet Stripe Raw Data.

Adapt this to your liking if you want the next import at another location.

As mentioned in the comments, you could change load_csv() to take the output destination as a parameter. If you also change it from Sub to Function, you can return the number of rows imported:

Function load_csv(rngDestination As Range) As Long
    '...

    With ThisWorkbook.Sheets("Stripe Raw Data").QueryTables.Add(Connection:= _
    "TEXT;" & fStr, Destination:=rng)

        '...

        .Refresh BackgroundQuery:=False
        load_csv = .ResultRange.Rows.Count

        '...

End Function

Now you can repeatedly call load_csv and provide it with the range where the output should begin for example:

Dim rngOutput As Range
Dim lngRows As Long

Set rngOutput = ThisWorkbook.Sheets("Stripe Raw Data").Range("$A$1")

lngRows = load_csv(rngOutput) ' load first file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load second file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load third file
lngRows = lngRows + load_csv(rngOutput.Offset(lngRows)) ' load fourth file

There is still much room for improvement:

• Removing duplicate headers
• Creating a loop instead of explicitly calling load_csv four times
• Better control for the user to select files (multiselect)
• Disconnecting the imported data from the QueryTable to reduce dependencies even after the import
• Not importing in ThisWorkbook but afterwards saving ActiveWorkbook - they may not always be the same
• ...

But that's not part of this question. After all, all you wanted to know was:

is there anyway to have it not override the data already imported?

I hope I could sufficiently answer this with the above.