'Importing information from a csv with headers into an excel sheet with a formatted table
I have been able to get opening a csv and importing the information into a new sheet while omitting the headers but cannot figure out how to import the information directly to the last empty row of a table within the sheet. I am able to select the last empty row of the formatted table and also create new empty rows within the table but can't push any information in.
Sub Append_CSV_File()
'Sheet = "Input Data"
'Table = "input_data" (dynamic formatted)
Dim csvFileName As Variant
Dim destCell As Range
Dim LO As ListObject
Dim C As Range
Dim rng As Range
Set rng = Range("input_data").Columns(1).Find(What:="", LookIn:=xlValues, LookAt:=xlWhole)
Application.ScreenUpdating = False
Set LO = Worksheets("Input Data").ListObjects("input_data")
With LO.Range.Columns("A") 'column_to_check is relative to the LO.Range
Set C = .Find(What:="*", After:=.Cells(1), LookIn:=xlValues, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not C Is Nothing Then
End If
End With
'ActiveSheet.ListObjects("input_data").ListRows.Add AlwaysInsert:=True 'Always insert new row
'Set the destination cell below, ideally in the next empty row of the table
Set destCell = Worksheets("Input Data").Cells(Rows.Count, "A").End(xlUp).Offset(1)
'WORKING but pastes to the row below the last row of the table
'Worksheets("Input Data").Cells(Rows.Count, "A").End(xlUp).Offset(1)
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
