Invoice number and auto increment


At this moment i have a Excel sheet With 2 macro’s on it that i want to convert to a libre calc form.

Is this possible and could anyone tell me how to translate the macro code?

I have 2 buttons Connected to the following macro’s
Sub nextinvoice()
Range(“e18”).Value = Range(“e18”).Value + 1
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
NewFN = “C:aaaInv” & Range(“e18”).Value & “.xlsx”
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
End Sub

The first macro empties the Fields and sums the number in cell E18 With 1.
The other macro saves the xlsm file to a xlsx file som the original file isn’t changed. Is this possible in libre and how?

Please format your code. Use four spaces at the beginning of each line of code, or backticks (```) for inline code. Also, make your question more to the point: “How can I translate macro code in an Excel file to LibreOffice macro code?”

Do you mean something like this?

Sub SaveInvWithNewNameAndNextInvoice 
Dim oActiveSheet As Variant
Dim oCellByPosition As Variant
Dim nValue As Double
Dim args2(1) As New
	oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
	oCellByPosition = oActiveSheet.getCellByPosition(4, 17)
	nValue = oCellByPosition.getValue()
	args2(0).Name = "InteractionHandler"
	args2(0).Value = ""
	args2(1).Name = "FilterName"
	args2(1).Value = "MS Excel 97" 
	ThisComponent.storeToURL(ConvertToURL("C:\aaaInv" & nValue & ".xls"), args2)
End Sub

That didn’t do the trick.
I am now trying to take 1 step at a time so first the increment action.

For incrementing the number i tried this, but this is not working as planed.
I have a macro With the following text:

    sub Increment
Sheet = thiscomponent.sheets.gebyname("sheet1")
cell = sheet.getcellrangebyname("E8")
end sub

This doesn’t work perhaps because it’s a code from OO, but as far as i know this is the same type of code for Libreoffice.

Maybe you have the error in the sheet name?

@JohnSUN, thank you for the answer, checked that, but sheet1 is like in the code “sheet1” I made the macro and a button but the buttonclick doesn’t do anything.

Oops! Do you really write “gebyname”? Not “geTbyname”?

Try this:

Sub Increment
Dim oCellRangeByName As Variant
  oCellRangeByName = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("E8")
End Sub

It still seems to be a problem to get one of these macro’s to work on my libreoffice. Does anyone have an idea on how to get this to work or possible a working template?

Thank you for the answer i will check thisn and see if it Works. saving the file should be to ODS.

In this case the code becomes shorter. You can delete all the five rows with args2 and use Array() as parameter at StoreToURL

Johnsun thanks for the code but I am having a little problem. On my invoice, the number increments perfectly with your code but I cannot get it to save the document where I want. I am running Debian 8 linux so don’t know if that makes any difference. Anyway, I have tried changing the 'thiscomponentstoretourl line but I don’t know enough about macro code to do it correctly. Here is where I want to store the saved sheet: /mybiz/receipts/. Can you help?

Sorry, just figured it out. I had a typo in the directory path.