asked on

VBA add csv to existing worksheet using file explorer

I have a weekly report that will add csv files to the existing workbook. I will likely use a button to run the process.

I would like for the code to open the file explorer so the user could choose which csv file to import and then add that file to the first blank row in the the sheet 'ur data'.
Can anyone help? Thank you!

Fabrice Lambert

Try the following:

Option Explicit

Public Sub ImportData()
    Dim Wb As Excel.Workbook
    Set Wb = ThisWorkbook
    
    Dim Ws As Excel.Worksheet
    Set Ws = Wb.Worksheets("ur data")
    
        '// retrieve the last row
    Dim Rng As Excel.Range
    Set Rng = Ws.Range("A1").EntireColumn
    Set Rng = Rng.Cells(Rng.Cells.Count).End(xlUp).Offset(rowOffset:=1)
    
        '// file picker dialog
    Dim path As String
    path = SelectFile
    If (path <> vbNullString) Then
        ImportCSV csv_file_name, Ws, Rng, "CSVData"
    End If
End Sub

    '// inspired from:
    '// https://renenyffenegger.ch/notes/Microsoft/Office/Excel/Object-Model/QueryTable/CSV
Private Sub ImportCSV(ByVal path As String, ByRef Ws As Worksheet, ByRef Rng As Range, ByVal TableName As String) ' {
    With Ws.QueryTables.Add(Connection:="TEXT;" & path, Destination:=Rng)
        .Name = TableName
        .FieldNames = True
        .RowNumbers = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .PreserveFormatting = True
        .TextFileCommaDelimiter = True
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .SaveData = False
        .TextFilePromptOnRefresh = False
        .TextFileTrailingMinusNumbers = True
        '// .textFileTabDelimiter         = false
        .TextFileSemicolonDelimiter = True
        '//.textFileSpaceDelimiter       = false
        '// .textFileColumnDataTypes      = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        '//.refreshStyle                 = xlInsertDeleteCells
        '// .adjustColumnWidth            = True
        '// .refreshPeriod                = 0
        .Refresh BackgroundQuery:=False
    End With
End Sub

Private Function SelectFile() As String
    Dim Dlg As Office.FileDialog
    Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
    Dlg.Filters.Add "CSV file", "*.csv"
    Dlg.InitialFileName = ThisWorkbook.path & "\"
    Dlg.AllowMultiSelect = False

    If (Dlg.Show) Then
        SelectFile = Dlg.SelectedItems(1)
    End If
End Function

Open in new window

side note:
Adjust the ImportCSV function to fit your needs

Euro5

ASKER

I am getting an error - Run-time error '1004'

Can I ask you to change this slightly?
Could it just pull in the ur.csv file (from the current folder), and add it to the first blank line in the sheet 'ur data'?
Not sure if that is easier or harder! Thank you!

Fabrice Lambert

I am getting an error - Run-time error '1004'

Wich line throw the error ?

Could it just pull in the ur.csv file (from the current folder), and add it to the first blank line in the sheet 'ur data'?

Can you upload your CSV file ?
The file structure might differ on my system.

Euro5

ASKER

This-is-the-workbook.xlsx

Fabrice,
I uploaded a workbook and a csv. This would be a good example of what I need to achieve.
The csv needs to compile at the end of the existing data. The file will always be called ur.
I would like to have it run the subroutine to complete this.
I really appreciate your help!
ur.csv

ASKER CERTIFIED SOLUTION

als315

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Euro5

ASKER

als315 - thank you!! This is working great to pull in the file!
2 changes if possible?

It is pulling in the header from the csv - can we not include that?
Can we not use the picker and just pull in the file ur.csv?

I really appreciate your help!

Euro5

ASKER

I figured it out! Thank you!