Code to Append CSV File to Active Sheet Help

Creating a macro - Writing a Script - Using the API

Code to Append CSV File to Active Sheet Help

Postby linuxology » Thu Aug 01, 2019 5:40 pm

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
Last edited by robleyd on Fri Aug 02, 2019 2:02 am, edited 1 time in total.
Reason: Added Code tags
OpenOffice 2.4 on Ubuntu 9.04
linuxology
 
Posts: 1
Joined: Thu Aug 01, 2019 5:21 pm

Re: Code to Append CSV File to Active Sheet Help

Postby Villeroy » Thu Aug 01, 2019 7:55 pm

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27292
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests