Invoice number and auto increment

[RJB]

Hello,

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
Range(“A21:d30”).ClearContents
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = “C:aaaInv” & Range(“e18”).Value & “.xlsx”
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
nextinvoice
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?

[bencomp]

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?”

[JohnSUN]

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 com.sun.star.beans.PropertyValue
	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)
	oActiveSheet.getCellRangeByName("A21:D30").clearContents(7)
	oCellByPosition.setValue(nValue+1)
End Sub

[RJB]

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")
cell.value=cell.value+1
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.

[JohnSUN]

Maybe you have the error in the sheet name?

[RJB]

@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.

[JohnSUN]

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

[JohnSUN]

Try this:

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

[RJB]

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?

[RJB]

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

[JohnSUN]

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

[wahfun]

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?
thx

[wahfun]

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