Hello
The code below imports a csv file into the Excel document at the last blank row and then sorts the various columns and also ensures that the formula in columns AA:AI are also copied down with the new data.
All works well on my computer BUT when importing data from another computer the date/time fields are being imported as text. The data that is in the document is m-dd-yyyy hh:mm:ss. When a new month is imported the date shows as 05/20/2020 11:17 but when I right click on it, the format is described as general. However, if I use isnumber(A2), it is false for new data but true for the old so I assume it isn't a number or date.
I even tried to convert the existing data to the mm/dd/yyyy hh:mm format but the new data still comes across as above and not recognized as a date. I want to avoid having to manipulate the csv data prior to importing if possible...can anything be added to the existing code so it formats accordingly? Thanks for any and all assistance.
The code below imports a csv file into the Excel document at the last blank row and then sorts the various columns and also ensures that the formula in columns AA:AI are also copied down with the new data.
All works well on my computer BUT when importing data from another computer the date/time fields are being imported as text. The data that is in the document is m-dd-yyyy hh:mm:ss. When a new month is imported the date shows as 05/20/2020 11:17 but when I right click on it, the format is described as general. However, if I use isnumber(A2), it is false for new data but true for the old so I assume it isn't a number or date.
I even tried to convert the existing data to the mm/dd/yyyy hh:mm format but the new data still comes across as above and not recognized as a date. I want to avoid having to manipulate the csv data prior to importing if possible...can anything be added to the existing code so it formats accordingly? Thanks for any and all assistance.
VBA Code:
Sub append_csv_file()
Dim csvfilename As Variant
Dim destcell As Range
Dim i As Variant
Dim ws As Worksheet
Dim Table As PivotCache
Set ws = Worksheets("Raw")
'this is setting an actual value for the row you want to add to
Set destcell = Worksheets("Raw").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
Worksheets("Raw").Select
With destcell.Parent.QueryTables.Add(Connection:="Text;" & csvfilename, Destination:=destcell)
.TextFileStartRow = 2
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(3, 3, 3, 2, 2, 2, 2, 3, 2, 2, 2, 2, 3, 3, 2, 2, 2, 2, 2, 2, 2, _
2, 2, 2, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
destcell.Parent.QueryTables(1).Delete
'sort descending order
With Worksheets("Raw").Sort
.SortFields.Clear
.SetRange Range("A2:X" & Cells(Rows.Count, "W").End(xlUp).Row)
.SortFields.Add Key:=Range("E2"), Order:=xlAscending
.SortFields.Add Key:=Range("G2"), Order:=xlAscending
.SortFields.Add Key:=Range("T2"), Order:=xlAscending
.SortFields.Add Key:=Range("S2"), Order:=xlAscending
.SortFields.Add Key:=Range("C2"), Order:=xlAscending
.Header = xlNo
.Apply
End With
ws.Range("AA2:AI2").AutoFill Destination:=ws.Range("AA2:AI" & Cells(Rows.Count, "W").End(xlUp).Row)
End Sub
Last edited by a moderator: