Ask Your Question
0

Invoice number and auto increment [closed]

asked 2014-06-19 12:49:05 +0200

RJB gravatar image

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 flag offensive 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

Comments

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

bencomp gravatar imagebencomp ( 2014-06-20 21:41:40 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2014-06-19 14:16:49 +0200

JohnSUN gravatar image

updated 2014-06-19 14:32:12 +0200

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
edit flag offensive delete link more

Comments

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.

RJB gravatar imageRJB ( 2014-06-25 08:43:13 +0200 )edit

Maybe you have the error in the sheet name?

JohnSUN gravatar imageJohnSUN ( 2014-06-25 14:36:13 +0200 )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.

RJB gravatar imageRJB ( 2014-06-25 15:01:09 +0200 )edit

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

JohnSUN gravatar imageJohnSUN ( 2014-06-25 15:57:54 +0200 )edit

Try this:

Sub Increment
Dim oCellRangeByName As Variant
  oCellRangeByName = ThisComponent.getSheets().getByIndex(0).getCellRangeByName("E8")
  oCellRangeByName.setValue(oCellRangeByName.getValue()+1.0)
End Sub
JohnSUN gravatar imageJohnSUN ( 2014-06-26 08:22:42 +0200 )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?

RJB gravatar imageRJB ( 2014-06-30 19:37:12 +0200 )edit
0

answered 2014-06-20 07:23:46 +0200

RJB gravatar image

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

edit flag offensive delete link more

Comments

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

JohnSUN gravatar imageJohnSUN ( 2014-06-20 10:07:16 +0200 )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

wahfun gravatar imagewahfun ( 2015-08-06 22:10:24 +0200 )edit

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

wahfun gravatar imagewahfun ( 2015-08-06 22:35:25 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2014-06-19 12:49:05 +0200

Seen: 1,830 times

Last updated: Jun 20 '14