Workbooks(1).Activate doesn't work with VBA macro in LO-Calc?

[therob]

Hey,

I have a VBA-Macro which in principle is working in LibreOffice when it is saved within a *.xlsm document - it makes what it should (copy some values between one worksheet to another worksheed of the same document=workbook).
It starts like this:

Option VBASupport 1
Sub MyMacro1()
  Dim Source As Worksheet 
  Dim Target As Worksheet
  Set Source = ActiveWorkbook.Worksheets(1)
  Set Target = ActiveWorkbook.Worksheets(2)
  '..... copying some cells ..... works! ....
End Sub 

Now I want not anymore to store the macro code within the xlsm-document but want to move it to the “my macro” section within the “BASIC-Macro” dialogue. So I copied the working macro code to a new macro within “MyMacros → MacroNameXY → Module1” and MyMacro1 is shown up and I can edit it.

But I can’t figure out how to add the part to first activate the only open workbook to further run my code within it. I thought I only need to pick the correct workbook and to make it active and then the rest of my code should work as before. I tried this but without success:

Option VBASupport 1
Sub MyMacro1()
  Dim Source As Worksheet
  Dim Target As Worksheet
  Dim wb As Workbook

  Set wb = Workbooks("myfile.xlsx")
  wb.Activate 
  Set Source = ActiveWorkbook.Worksheets(1)
  Set Target = ActiveWorkbook.Worksheets(2)
  '..... copying some cells ..... NOT Working ....
End Sub 

I get the error message (translated german–>eng):
“BASIC Runtime error.
‘35’
Procedure Sub or Function not defined.
Additinal information: Workbooks”
And the code line Set wb = Workbooks("myfile.xlsx") is highlighted.

Can anybody help my how to proceed?

My main goal is just to run me working script which is not saved in the workbook/file to process but which is stored in the “MyMacro” of OO place (where ever this exactly is) - but I think I have to activate first the only open workbook… but how?

What I indeed first tried to just run the first macro as it is like this:

Sub MyMacro()
  Dim Source As Worksheet
  Set Source = ActiveWorkbook.Worksheets(1)
  '......
End Sub

But it ends with the error message at the line Set Source = ActiveWorkbook.Worksheets(1):
“BASIC Runtime error.
‘91’
Object variable not assigned”
Therefore I tried the first mentioned activate procedure… but without luck…

Thanks for any hints where a knot in my mind is…
Cheers, Robert

[fpy]

@therob

"myfile.xlsx"

full path ?

[therob]

Unfortunately no change, have still the error “35…”.
It seems that I can write anything I want inside Workbooks(…) and always get the same error - so the problem seems to be one step before, as it does not recognise the “Workbooks” routine itself.
But why?

Could maybe someone post a minimal example of VBA-Macro for LO (24.8 under Linux Mint in my case) which activates an xlsx-file (either opened (indexed) or saved on disk (full path)) and which is saved in the “My macro” entry?
At least this should run on my system before I will find any error in my macro…

[Zizi64]

@therob

Could maybe someone post a minimal example of VBA-Macro for LO

Do not use VBA for the LibreOffice. LO has not full compatible with it.
Use the native API functions of the LibreOffice and use one of the supported programming languages (StarBasic for example). API: Application Programming Interface.

[therob]

Hm… I am aware of this. But the thing is, that the VBA-Script itself is run perfectly if I save it into the XLSM-File and run it as cited. There I thought it should not be a big step to run it from another place and pointing it to the right file - am I so wrong?

Because I do some Python-scripting with the xlsm-file the included VBA-code is deleted, therefore I want to store it in “My Macro” and just want to tell what file to activate…that difficult?

[fpy]

@therob

why?

@Zizi64

not full compmpatible

Like in VBA ( MSOffice -Macro ) ThisWorkBook and ActiveWorkBook , Are there any Similar Way in STAR BASIC -Calc Marco there? - #10 by mikekaganski

[therob]

hm… read this thread before.

I just tried: I will solve my problem directly in Python/openpyxl prior to my main script and avoid VBA/BASIC macros in this case at all.

I find it sad not to have a solution to my original post within VBA, but I think I am happy now to make it directly in python.

anyway thanks for your posts!

[Villeroy]

LibreOffice can open /path/myfile.xyz and /other_path/myfile.xyz at the same time.
Therefore Set wb = Workbooks("myfile.xlsx") does not identify a distinct document.
Solution:

wb = StarDesktop.loadComponentFromURL(ConvertToURL("/path/myfile.xlsx"), "_default", 0, Array())

which loads the right document if necessary.