Page 1 of 1

Code to Append CSV File to Active Sheet Help

PostPosted: Thu Aug 01, 2019 5:40 pm
by linuxology
The following code will take a csv file and append the data to the last line in excel. Think of the purpose being financial transactions. It is tested and works in excel. It does not work in libre office. The reason may be due to he pop up dialog that allows one to choose which CSV file to import. I am fine with changing this to a specific file location and file name. Would someone help with conversion?

Code: Select all   Expand viewCollapse view
Sub ImportCSV()
On Error GoTo Over
LRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Application.ScreenUpdating = False
ActiveSheet.Unprotect
For Each QTable In ActiveSheet.QueryTables
QTable.Delete
Next
InFile = False
ChDir "C:\Users\"
InFile = Application.GetOpenFilename
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & InFile, Destination _
:=Range("A" & LRow))
If InFile = False Then
MsgBox ("You Did Not Choose An Input File")
GoTo Over
End If
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Over:
Application.ScreenUpdating = True
End Sub

Re: Code to Append CSV File to Active Sheet Help

PostPosted: Thu Aug 01, 2019 7:55 pm
by Villeroy