Import csv file and append to existing table?

[shellp]

Hello,

The code below works to import my .csv file into Excel onto the RawData_Dis worksheet but I want to change the data on worksheet RawData_Dis into a table and can't find VBA examples to append the new data to the existing table. Note that there are formulae in columns P to X that need to be copied down with the appended data. Any and all assistance to edit the code for appending new data to the table is greatly appreciated.

Private Sub Import_data_dis()
Dim csvfilename As Variant
Dim destcell As Range
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet

Set ws1 = Sheets("RawData_Dis")
'Set ws2 = Sheets("Data")
Set ws3 = Sheets("Info")

Set destcell = ws1.Cells(Rows.Count, "A").End(xlUp).Offset(1)

chDrive_str = Sheets("ReadMe").Range("C3:C3").Value

Set ofs = CreateObject("scripting.filesystemobject")
ChDrive ofs.getdrivename(chDrive_str)
ChDir chDrive_str

Application.ScreenUpdating = False

csvfilename = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv),*.csv", Title:="Select a CSV File", MultiSelect:=False)
If csvfilename = False Then Exit Sub

ws1.Visible = -1
ws1.Select

With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 4, 4, 4, 4, 2, 2, 2, 2, 2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False

End With

last_row = ws1.Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Info").Range("J4:J4").Value = last_row - 1

'this is correct but only if in rawdata worksheet
ws1.Range("P2:X2").AutoFill Destination:=ws1.Range("P2:X" & Cells(Rows.Count, "A").End(xlUp).Row)

ws1.Visible = 0

destcell.Parent.QueryTables(1).Delete

Application.ScreenUpdating = True
End Sub

 

[alansidman]

Use Power Query?

 

[shellp]

Thanks....but I was hoping to automate the process and I don't think I can do that with power query, correct?

 

[shellp]

Just came back to thank you alansidman for your suggestion. I didn't know much about power query and your suggestion set me off on a journey to learn about it. I have now reformatted my entire scorecard based on this method and it changes the way I will develop/automate scorecards in the future. Thank you so much.

 

[alansidman]

Happy to hear your success. Thanks for the feedback. You can do a lot with PQ. If you already haven't, you may want to pick up Ken Puls and Miguel Escobar's book.

Master Your Data with Power Query in Excel and Power BI

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time...

www.mrexcel.com

 

[shellp]

Thanks!! I was looking for reference material for this...maybe I'll ask Santa