# Invoice number and auto increment [closed]

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?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2016-02-25 22:09:15.825568

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

( 2014-06-20 21:41:40 +0100 )edit

Sort by » oldest newest most voted

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

more

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.

( 2014-06-25 08:43:13 +0100 )edit

Maybe you have the error in the sheet name?

( 2014-06-25 14:36:13 +0100 )edit

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

( 2014-06-25 15:01:09 +0100 )edit

Oops! Do you really write "gebyname"? Not "geTbyname"?

( 2014-06-25 15:57:54 +0100 )edit

Try this:

Sub Increment
Dim oCellRangeByName As Variant
oCellRangeByName = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("E8")
oCellRangeByName.setValue(oCellRangeByName.getValue()+1.0)
End Sub
`
( 2014-06-26 08:22:42 +0100 )edit

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?

( 2014-06-30 19:37:12 +0100 )edit

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

more

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

( 2014-06-20 10:07:16 +0100 )edit

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

( 2015-08-06 22:10:24 +0100 )edit

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

( 2015-08-06 22:35:25 +0100 )edit