How can I auto import a csv to a growing list in excel?

Question

Everyday I receive a report in a .csv format, and on a daily basis I will copy the data into an "INPUT" tab on my spread sheet. The data on this input tab keeps on growing every day with the information and I will import this data by:

• Clicking the last row on the "INPUT" tab
• Get external text data
• Import the CSV with ; as my delimiter and inputting the data from row 2 on wards
• Format the text to font Calibri 8pt

The CSV files that come in all have a date incrementing naming convention list so:

20130826_Summary_Report.csv 
20130827_Summary_Report.csv
20130828_Summary_Report.csv

Were probably looking at about 5000 lines of data a day, and while it isn't the most time consuming task, we're looking at ways to simplify the process.

Is there any way I can create a macro which will grab the latest .csv file out of a folder and import the data using the process above automatically?

Answer 1

You can use the Workbooks.OpenText method to input in a new worksheet, then do Worksheets.Move method to bring into the current workbook (even after the "INPUT" tab).

Since you mentioned you do this daily, a way to automatically pull in today's .csv file would be:

Dim inputfilepath As String
inputfile = ActiveWorkbook.Path & "\" & Format(Date, "yyyymmdd") & ".csv"
'open file
Workbooks.OpenText Filename:=inputfile

You can specify parameters for .OpenText that set the .csv delimiters and other options that fit your .csv file.

You could then start copying to the last row in the "INPUT" tab by automatically finding the last row using the Range.end method:

Worksheets("INPUT").Range("A1").End(xlDown).Offset(1).Select

This assumes you have no gaps in information in column A, and goes to the cell after the last cell with information - the same as selecting A1, then doing Ctrl-down.