Button association failed after save and open again

Creating a macro - Writing a Script - Using the API

Button association failed after save and open again

Postby gutee » Thu Jan 24, 2019 4:42 pm

I have a button in spreadsheet and associate it to run a macro, it works well.
After I save and close the file, button is still there, but the link to run the macro is missing, I have to update the button again.
Could you share how to debug such case?
Thanks.
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

Re: Button association failed after save and open again

Postby Zizi64 » Thu Jan 24, 2019 8:23 pm

Where is the macro code? In the document, or in the MyMacros/Standard library of the ofice suite?
What a file type are you using for your spreadsheet document?

Can you upload your sample file and the macro code (embedded into the document) here?
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Button association failed after save and open again

Postby gutee » Fri Jan 25, 2019 12:03 pm

It is inside the My_Macros.
The file is named as .xls
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

Re: Button association failed after save and open again

Postby Lupp » Fri Jan 25, 2019 1:18 pm

As far as I know (I cannot quote a source.) AOO (and also LibO) macros are never stored into MS-style files. Assuming a user will only save to MS formats if they want to send the files to partners working with MS office (TM), this is completely reasonable since MS software surely will not execute such macros.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Button association failed after save and open again

Postby Zizi64 » Sat Jan 26, 2019 12:31 am

It is inside the My_Macros.
The file is named as .xls


There is not (never was and never will be) 100% compatibility with the foreign and obsolete file formats like the binary MS .xls file type, and the VBA macros. The Excel can work with the VBA macros. The StarBasic + API of the AOO/LO is not VBA, therefore the AOO/LO will not embed any StarBasic+AOO/LO API related macros, or the macro related references into an MS .xls file.

Always use the native, international standard ODF file formats (.ods for Calc) in the AOO and in the LO for your important documents.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Button association failed after save and open again

Postby gutee » Sat Jan 26, 2019 5:55 am

If my file (spreadsheet with macros) needs to work in Windows_Excel and Linux_OpenOffice, which format should it be ?
.xls
.xlsm
.ods
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

Re: Button association failed after save and open again

Postby Zizi64 » Sat Jan 26, 2019 11:23 am

If my file (spreadsheet with macros) needs to work in Windows_Excel and Linux_OpenOffice, which format should it be ?

it is depend on the functionality of the macro. The pure arithmetical macros will run in each spreadsheet application:

Code: Select all   Expand viewCollapse view
function paralell_resistors(A as double, B as double) as double
     if A+B= 0 then
          paralell_resistors = 0
     else
          paralell_resistors = A*B/(A+B)
     end if
end function


Note: The application controlling commands ARE NOT COMPATIBLE between the MS and the open source office suites!
Use the ODF fileformats and the Starbasic+API functions and subroutines if you are working in AOO or LO,
but use the VBA and .xls, xlsm, xlsx fileformats when you work in MS Excel.
That means: you MUST totally rewrite all of application controlling macros when you want to port a project from open source to the MS world.

Do not try to make an .xls, xlsm file containing VBA macros in the Apache OpenOffice or in LibreOffice.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7768
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Button association failed after save and open again

Postby Lupp » Sat Jan 26, 2019 1:25 pm

Zizi64 wrote:The pure arithmetical macros will run in each spreadsheet application...

I suppose so - If I insert the identical code under the respective application.
(Lacking access to any Excel I cannot test, but also "pure" arithmetical functions programmed with AOO-Basic may contain one or another element not impemented the same way in Excel-VBA. Of course, any attempt to use a FunctionAccess service will fail though not accessing any of the documents properties.)

But:
I could test for the results of storing a Calc document containing a very simple arithmetic user function with any of the MS.Excel formats offered by AOO 4.1.5 (.xls 95 and 97) and in addition by LibO 6.1.4 (.xlsx 2007 through 2019).
My user code was not saved to the files, nor got I offered an option to that effect.

The sample code:
Code: Select all   Expand viewCollapse view
Function square(pBasis As Double) As Double
square = pBasis * pBasis
End Function

The very narrow road for moving Basic code between Excel and Calc by saving to the other "format" seems to be srictly one way.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Button association failed after save and open again

Postby gutee » Mon Jan 28, 2019 3:26 am

I will try to use .xlsm format and make necessary branches in the macro codes.
when the user use the file in Windows, they can update the spreadsheet and run the macros.
when the user use the file in Linux, they can also open the .xlsm with Openoffice, update spreadsheet and run the macros too.
I guess basic codes (vars, flow-controls,..) should working in both MS-Excel and Linux-Openoffice, but for dedicated service, I should try to find the solution to map them into MS-Excel.
e.g. following code in Openoffice :
Global aPoint As New com.sun.star.awt.Point
Global aSize As New com.sun.star.awt.Size
oRectangleShape = oDocument.createInstance("com.sun.star.drawing.RectangleShape")
OpenOffice.org.3.3.0
gutee
 
Posts: 19
Joined: Wed Jan 09, 2019 9:12 am

Re: Button association failed after save and open again

Postby JeJe » Mon Jan 28, 2019 12:56 pm

If you want to convert OpenOffice structs to something you can use in VBA, create a user type and add the same elements that are in the Struct which you find here:

https://www.openoffice.org/api/docs/com ... /Size.html
https://www.openoffice.org/api/docs/com ... Point.html

Result:

Public Type Point
x As Long
y As Long
End Type

Public Type Size
width As Long
height As Long
End Type

Edit: You can use any name but Point is identical to the Windows "Pointapi" type so you might want to call it that.

Edit2: But something that uses an OO Struct is unlikely to have an Excel equivalent something. As was suggested in the other thread you should be thinking of writing VBA from scratch that does something equivalent in Excel rather than translating any code from OO.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 428
Joined: Wed Mar 09, 2016 2:40 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests