Page 1 of 1
Convert VBA to Calc
Posted: Sat Feb 16, 2019 8:27 am
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
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 9:32 am
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.
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 9:50 am
by grahamxy
Thank you so much
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 9:57 am
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
statement in the Module before the VBA code.
I have not tried the another routine.
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 10:00 am
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?
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 10:14 am
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.
Re: Convert VBA to Calc
Posted: Sat Feb 16, 2019 7:39 pm
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.