Convert VBA to Calc

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
grahamxy
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Convert VBA to Calc

Post by grahamxy »

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

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Post by Zizi64 »

Code: Select all

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
grahamxy
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Re: Convert VBA to Calc

Post by grahamxy »

Thank you so much
OS X Yosemite Version 10.10.1 OpenOffice Version 4.1.1
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Post by Zizi64 »

Otherwise the macro

Code: Select all

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

Option VbaSupport 1
statement in the Module before the VBA code.
I have not tried the another routine.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
grahamxy
Posts: 41
Joined: Sat Dec 20, 2014 6:03 am

Re: Convert VBA to Calc

Post by grahamxy »

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
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Convert VBA to Calc

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Convert VBA to Calc

Post by Lupp »

A kind of translation from the Excel-VBA code above to AOO Basic with API usage woud be:

Code: Select all

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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply