Convert VBA from Excel Application

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
larrycavan
Posts: 1
Joined: Tue Mar 27, 2012 1:51 am

Convert VBA from Excel Application

Post by larrycavan »

I have an Excel workbook with VBA forms that pulls data from and writes data to an MS Access database. The application connects to an external electronic device that captures flow data. The data that configures the device is stored in the Access database. Data from the device is delivered live to the excel workbook where it's forumlated for usefulness. This workbook as a lot of VBA code to make everything work.

I'd like to have end users be able to run this application in Calc. I read that OpenOffice has a built in VB compatibility mode or add on that will allow Excel applications to run in Calc without converting all the VBA code. Did some research but no able to verify if this is possible.

I really don't want to have to start over with new coding from scratch. I'm not really a programmer but I've muddles my way through VBA effectively. Here's an example of some VBA code from the workbook for a combo box on a VBA form in the application. The form allows for .mdb table data changes to configure the electronic device.

Any help will be much appreciated.

Regards,
Larry

Will such an application run in Calc?
Is there a tool to convert all the VBA code to native openoffice code?

Code: Select all

Private Sub CboFlowRanges_Change()
DataqSdk1.Stop

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\program files\flowsoft\data\FlowSoft.mdb")
Set rs = db.OpenRecordset("configuration")

Orifice = rs.Fields(14)
CD = rs.Fields(16)

rs.Edit
rs!OrificeDiameter.Value = CboFlowRanges.Column(0)
rs!DischargeCoefficient.Value = CboFlowRanges.Column(1)
rs.Update
Call GetConfig
Call Start_Click

'place the CFM Range number from cboflowranges combo box on the worksheet
Worksheets("flowsoftexcel").Range("b5").Value = CboFlowRanges.Column(2)
TextCFMRange.Value = CboFlowRanges.Column(2)
                        
End Sub
Title Edited. A descriptive title for posts helps others who are searching for solutions and increases the chances of a reply (Hagar, Moderator).
openoffice 3.1 on windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel Application

Post by Villeroy »

Of course this can not run in OpenOffice. If you write programs control MS software then you need to run MS software. Simple as that. Can you tell me a single application that runs another applications macro code?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Convert VBA from Excel Application

Post by RoryOF »

The conversion from Excel VBA to OpenOffice code is not trivial; it has a high learning curve. There exist various code converters, but none are fully satisfactory.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Convert VBA from Excel Application

Post by rudolfo »

And here comes a third voice that says NO.
larrycavan wrote:[...]The application connects to an external electronic device that captures flow data.[...]
"Connects to an external device" are only 5 words, but they hide an awful amount of complexity. That's either a connection through TCP/IP or maybe even a connection that uses a RS 232 serial port (or something even more obscure). The VBA of MS Office can use Visual Basic COM objects and implements even event handling through COM/DCOM. The VBA compatibility mode of Calc is far away from this. It only supports some basic methods and properties of Excels VBA objects (spreadsheets, cells, ..). Whenever your VBA uses CreateObject() it is mostly not portable to OOo.
OOo doesn't need to mimic MS here, because OOo has superior languages for macro programming, that have advance libraries for networking and other more complicated stuff included in their core distribution, so OOo doesn't need to blow up Basic to something that feels more like a programming language as Microsoft did with VBA.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
makuck
Posts: 2
Joined: Thu May 10, 2012 10:27 pm

Re: Convert VBA from Excel Application

Post by makuck »

You have to convert it all manually because the programming languages simply don't talk to each other at all. I had to delete every combo box and macro and do the entire workbook over again. Functions you can leave alone but conditional formatting, macros, anything with visual basic or things you do in design mode will be nonfunctional otherwise.
OpenOffice 3.3 on Windows 7
Post Reply