I have a .csv file and a master excel file. The master file contains a table and I would like to automatically append the data in the .csv file to the existing table. The data has the same headers and column order. I have the folllowing VBA which appends the .csv data to the next row after the table fine, but the data is not part of the table:
Sub Append_CSV_File()
Dim csvFileName As Variant
Dim destCell As Range
Set destCell = Worksheets("Sheet1").Cells(Rows.Count,
"E").End(xlUp).Offset(1) 'Sheet1
csvFileName = Application.GetOpenFilename(FileFilter:="CSV Files
(*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
If csvFileName = False Then Exit Sub
With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName,
Destination:=destCell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
destCell.Parent.QueryTables(1).Delete
End Sub
There are also columns in the table to the right of the data that calculate a value from the imported data. Is there anyway to automatically have the formulas copied down the column when the new data is appended?
Best Answer
I had the same issue, and wanted to append several (16 to be precise) csv files in one listing. The Array I used is static and there are better ways of coding this, but I needed to collect specific files from a number of csv files that are within the folder location.
I found your code interesting, and updated the code that I had put together from other sources to get a set of code working.
Thanks for sharing your code, as you will see I've used an element of your code to find the next blank row to append to.
See below code example, you will need to add the filenames, and file directory path, and update the xFiles array to match the number of files that you want to import and append: