Convert VBA to Calc

Creating a macro - Writing a Script - Using the API

Convert VBA to Calc

Postby grahamxy » Sat Feb 16, 2019 8:27 am

Hi,
I have an Excel workbook that runs macros but I much prefer Calc and would like to run it on that. I am a complete novice at this as formulae are my limitations, I would like to know if :
1. Can this be converted and run on Calc
2. Would someone give me a little help in doing the conversion. (I feel this would take longer than I have left on this Earth) or
3. Would someone please convert it.
I would be so grateful for any advice given, please find my VBA scripts below

Code: Select all   Expand viewCollapse view
Sub NextQote2()
       Dim ws As Worksheet
       For Each ws In ActiveWorkbook.Worksheets
       ws.Range("B1").Value = ws.Range("B1") + 1
       
       ws.Range("A22,B6,D1,D2,D3,F20,F25,G24,G25,H25,I24,I25,I27,J20, J25,J27,K20,K21,L25").ClearContents
Next
End Sub

Sub SaveQoteWithNewName()

        Dim NewFN As Variant
        ' Copy Quote to a new workbook
        ActiveSheet.Copy
        NewFN = "iclouddrive/desktop/quotes/Quote" & Range("B1").Value & ".xlsx"
        ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
       
        NextQote2
       
End Sub
OS X Yosemite Version 10.10.1 OpenOffice Version 4.1.1
grahamxy
 
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Re: Convert VBA to Calc

Postby Zizi64 » Sat Feb 16, 2019 9:32 am

Code: Select all   Expand viewCollapse view
NewFN = "iclouddrive/desktop/quotes/Quote" & Range("B1").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook


Always use the nternational standard ODF fileformats. The AOO can not save in OOXML file format. The LibreOffice can, but always use the native fileformats.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; 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: 7830
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Postby grahamxy » Sat Feb 16, 2019 9:50 am

Thank you so much
OS X Yosemite Version 10.10.1 OpenOffice Version 4.1.1
grahamxy
 
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Re: Convert VBA to Calc

Postby Zizi64 » Sat Feb 16, 2019 9:57 am

Otherwise the macro

Code: Select all   Expand viewCollapse view
Sub NextQote2()
       Dim ws As Worksheet
       For Each ws In ActiveWorkbook.Worksheets
       ws.Range("B1").Value = ws.Range("B1") + 1
       
       ws.Range("A22,B6,D1,D2,D3,F20,F25,G24,G25,H25,I24,I25,I27,J20, J25,J27,K20,K21,L25").ClearContents
Next
End Sub

can run in my LO 6.1.5 without any modification or conversion, because the LO has a littlebit higher compatibility with the foreign file types and the VBA macros.

Just use the
Code: Select all   Expand viewCollapse view
Option VbaSupport 1

statement in the Module before the VBA code.
I have not tried the another routine.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; 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: 7830
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Postby grahamxy » Sat Feb 16, 2019 10:00 am

I will give Libre a try as I have just downloaded it, if I change the saved file format in the other macro will that work too?
OS X Yosemite Version 10.10.1 OpenOffice Version 4.1.1
grahamxy
 
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Re: Convert VBA to Calc

Postby Zizi64 » Sat Feb 16, 2019 10:14 am

if I change the saved file format in the other macro will that work too?

I do not know it. Try it...

Generally, it is better to rewrite all of your VBA macros based on the "StarBasic + API functions" of the AOO/LO.
Some VBA codes will run, but some will not run in LibreOffice.
(API: Application Programming Interface. It is a huge "function Library" for controlling the opensource office suites.)

The StarBasic is a very simple programming language and IDE. But you must call the API functions from the Basic (or from any other supported programming languages), and you must to pass exact programming parameters to them.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.2; 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: 7830
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Postby Lupp » Sat Feb 16, 2019 7:39 pm

A kind of translation from the Excel-VBA code above to AOO Basic with API usage woud be:
Code: Select all   Expand viewCollapse view
Sub demo
Const rgList As String = "A22,B6,D1:D3,F20,F25,G24:G25,H25,I24:I25,I27,J20,J25,J27,K20:K21,L25"
theDoc      = ThisComponent
theSheet    = theDoc.Sheets(0)
myMultiSel  = theDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
rgListSplit = Split(rgList, ",")
u = Ubound(rgListSplit)
For j = 0 To u
  myMultiSel.addRangeAddress(theSheet.getCellRangeByName(Trim(rgListSplit(j))).RangeAddress, False)
Next j
myMultiSel.clearContents(7) REM 7 = 1_VALUE + 2_DATETIME + 4_STRING
REM Constant content is cleared. Formulas, annotations, and everything else are not.
'myMultiSel.CellStyle = "csShow" REM Ran once to create the highlighting.
REM The cell style is only used for the demo here.
End Sub

Sub storeNextVersion
REM Do NOT use alien formats with AOO or LibO!
REM Behaviour encouraged by a hostile competitor may lead into trouble.
theDoc = ThisComponent
theSheet = theDoc.CurrentController.ActiveSheet
nameModifierCell = theSheet.getCellRangeByName("B1")
newURL = convertToURL("R:/iclouddrive/desktop/quotes/Quote" & nameModifierCell.String & ".ods")
REM R: was a mapping made on my system for the test.
theDoc.storeAsURL(newURL, Array())
theDoc.Close(True)
End Sub
andf if the task to solve actually requires such a macro it surely is better to use the OpenOffice/LibreOffice API than to accept to be forced to shift to LibO from AOO only for its "better" VBA support.

But: Can someone explain that need to me? I am using LibreOffice, AOO and their predecessors for a couple of decades now, and never saw an example of such a need. Clear design is always better than macros.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2369
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 1 guest