Import data from CSV to table
Attached is a sanitized version of something I am working. The next objective is to automate importing and appending the data from a CSV with a few parameters:
1. the existing data in the Log table should be deleted
2. the data from the CSV should be appended to the existing Log table
3. there should be no blank rows when finished
Below was something I grabbed from another sheet I have. That one however just imports the data to a blank sheet so there's no existing table to deal with.
#1 is addressed. #3 can be addressed by simply deleting all but one row of data though I'm unsure how to do this in VBA. When pasting to a table, Excel automatically makes it large enough to accommodate all the data.
#2 is really where my meager VBA fails. Running as is, the data is inserted to the left of the table. I suppose what could be one is to have the table deleted entirely, import the data, and then create the table again. But if anyone has any ideas how I can eliminate that need, I'd appreciate it.
Sub CSV_Import()
Dim ws As Worksheet, strFile$
Selection.AutoFilter
Range("Log").Select
Selection.ClearContents
Set ws = ActiveWorkbook.Sheets("Log") 'set to current worksheet name
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub
|