Wrong execution of VBA

[Pia1]

I wrote a VBA for excel to copy the last row of all csv-tables in one selected folder to a single excel sheet to compare them without opening every csv-table separately. Unfortunately, I noticed that I have to use libre office and therefore hoped to get LibreOffice to execute the VBA after I enabled the VBA support option. Then I run the VBA within LibreOffice it just opens all the csv-tables and closes them again. May someone with some experience in coding macros in LibreOffice tell me how I have to change the code to get the desired behaviour? In the following the VBA code and an image of how the result should look like:

Thank you so much for your help!

Rem Attribute VBA_ModuleType=VBAModule
Option VBASupport 1
Sub Merge()
Dim TargetTable As Object
Dim SourceTable As Object
Dim Path As String
Dim file As String
Dim lastRow As Long
Dim SourceRng As Range
Dim lastRowTgt As Long
Dim sheetName As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set TargetTable = ActiveWorkbook                'open workbook is target

Path = InputBox("Enter Path", "Path")           'enter Path into Box
file = Dir(CStr(Path & "*.csv*"))               'all files in csv-format

Do While file <> ""                             'do as long until all files are done

Set SourceTable = Workbooks.Open(Path & file, False, True) 'open only as readable
sheetName = Left(file, Len(file) - 4)
With SourceTable.Sheets(sheetName)
    lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row 'assumes column 1 is contiguous
    Set SourceRng = .Range("A" & lastRow & ":AJ" & lastRow)
End With
With TargetTable.Sheets("Merge.xlsm")
    lastRowTgt = .Cells(.Rows.Count, 1).End(xlUp).Row 'assumes column 1 is contiguous
    SourceRng.Copy .Cells(lastRowTgt + 1, 1)
End With

SourceTable.Close
file = Dir()

Loop


Application.ScreenUpdating = True
Application.DisplayAlerts = True


MsgBox "Files have been merged!"

Set TargetTable = Nothing
Set SourceTable = Nothing
End Sub

[karolus]

it would be more helpfull to know what sign is used as delimiter in the *.csv-files, than spamming us with crumpy VBA

[JohnSUN]

@karolus It was Excel, so delimiter was comma and codepage was 1251… IMHO

[ajlittoz]

Please retag your question to replace meta (issues with this site) with calc (problems with the spreadsheet component Calc as I assume your question is related).

Since the site engine is not user friendly, to exit retag mode, type a space to be away of any tag then press Enter twice.

[JohnSUN]

I hope that short comments will be enough for you to understand how it works:

Option Explicit

Sub SummaryRowsCSVFiles()
Rem You can get the folder path using InputBox. But there is a way to simplify the user experience:
Dim oFolderDialog As Object
Dim oUcb As Object 
Dim sPath As String
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	oFolderDialog = CreateUnoService("com.sun.star.ui.dialogs.FolderPicker")
	oUcb = createUnoService("com.sun.star.ucb.SimpleFileAccess")
	oFolderDialog.SetDisplayDirectory(GetPathSettings("Work"))
	If oFolderDialog.Execute() = 1 Then
		sPath = oFolderDialog.GetDirectory()
	Else
		Exit Sub 
	End If
Rem sPath is your folder. Which CSV-files there?
Dim aFiles As Variant 
	aFiles = ReadDirectories(sPath, True, False, True, Array("csv"))
Rem aFiles - list of all CSV-files. If empty - stop
	If UBound(aFiles) < LBound(aFiles) Then Exit Sub 
Rem Spreadsheet for temporary opened files and for Result
Dim oTempDoc As Variant 
	oTempDoc = CreateNewDocument("scalc")
Dim oSheets As Variant 
	oSheets = oTempDoc.getSheets()
Dim i As Long 
Rem We need two sheets only - remove other:
	For i = oSheets.getCount()-1 To 1 Step -1
		oSheets.removeByName(oSheets.getByIndex(i).getName())
	Next i
Rem If only single sheet - add second:
	If oSheets.getCount()=1 Then oSheets.insertNewByName("Temp",1)
Dim oResSheet As Variant, oTempSheet As Variant
	oResSheet = oSheets.getByIndex(0)
	oTempSheet = oSheets.getByIndex(1)
Rem Create HEADER on oResSheet:
Dim oCell As Variant 
	oResSheet.getCellByPosition(0,0).setString("FileName")
	oCell = oResSheet.getCellByPosition(1,0).getCellAddress()
Dim oCursor As Variant, aRange As Variant 
Rem About .link() see http://www.openoffice.org/api/docs/common/ref/com/sun/star/sheet/XSheetLinkable.html#link
Rem About Filter Options see https://wiki.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Filter_Options
Rem or https://stackoverflow.com/a/66670398/14094617
	oTempSheet.link(aFiles(0,0),ConvertFromURL(aFiles(0,1)), _
		"Text - txt - csv (StarCalc)","44,34,34,1,,0,false,true,true,false,false", _
		com.sun.star.sheet.SheetLinkMode.VALUE)
Rem Here we can not use .End(xlUp).Row, here it will done with Cursor:
	oCursor = oTempSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	aRange = oCursor.getRangeAddress()
	aRange.EndRow = aRange.StartRow
	oResSheet.copyRange(oCell, aRange)
Rem Copy data from CSVs:
	For i = LBound(aFiles) To UBound(aFiles)
		oResSheet.getCellByPosition(0,i+1).setString(FileNameoutofPath(ConvertFromURL(aFiles(i,0))))
		oTempSheet.link(aFiles(i,0),ConvertFromURL(aFiles(i,1)), _
			"Text - txt - csv (StarCalc)","44,34,34,1,,0,false,true,true,false,false", _
			com.sun.star.sheet.SheetLinkMode.VALUE)
		oCursor = oTempSheet.createCursor()
		oCursor.gotoEndOfUsedArea(True)
		aRange = oCursor.getRangeAddress()
		aRange.StartRow = aRange.EndRow
		oCell.Row = i + 1
		oResSheet.copyRange(oCell, aRange)
	Next i
Rem Remove Temp sheet
	oSheets.removeByName(oTempSheet.getName())
End Sub

[Pia1]

Thank you so much! It works just fine and your comments helped a lot!

[karolus]

@JohnSUN just for testing I run your code on a Folder with only 5 csv-files, 3 of them with less than 10 Lines, 2 with roundabout 35000 ! Lines. the Execution-time was ~5 Seconds.

My own solution without invoking Libre-Calc needs less than 50 Milliseconds, thats 100times faster.
See Answer:

[JohnSUN]

@karolus Yes, your solution is both shorter and faster (Python is generally faster than Basic).

In my answer, I started from the text of the question: @Pia1 can write and read code in the VBA.
Accordingly, it will be much easier to read and understand the StarBasic code than to disassemble constructs like lastline = line.strip() or lastline. @Pia1 write that the techniques that were used in the VBA (for example, finding the last non-empty row) do not work in Calc - it was necessary to demonstrate some of the ways to use the API, namely the use of LibreOffice tools, and not just repackaging data from text files to text file.

Your code is good, very good. This solves the problem (but does not answer questions that were not asked, but implied)

[karolus]

from pathlib import Path

def main():

    csvfiles = Path('/home/pi/Downloads/').glob('*.csv')

    outfile = Path('/home/pi/') / 'csvdump.csv'

    with outfile.open('w', encoding='utf8') as dumpfile:
        for p in csvfiles:
            dumpfile.write(f""""{p.name}"; {lastline(p)}\n""")
        
        
def lastline(path):
    with path.open(encoding='utf8') as sourcefile:
        lastline = '--sorry file is empty---'
        for line in sourcefile:
            lastline = line.strip() or lastline
    return lastline

%timeit main()

---

46.7 ms ± 506 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)