Convert VBA from Excel Application

Creating a macro - Writing a Script - Using the API

Convert VBA from Excel Application

Postby larrycavan » Tue Mar 27, 2012 2:01 am

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   Expand viewCollapse view
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
larrycavan
 
Posts: 1
Joined: Tue Mar 27, 2012 1:51 am

Re: Excel Application

Postby Villeroy » Tue Mar 27, 2012 12:21 pm

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 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17246
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Convert VBA from Excel Application

Postby RoryOF » Tue Mar 27, 2012 12:28 pm

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.0.1 on Xubuntu 13.10 and Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 14328
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Convert VBA from Excel Application

Postby rudolfo » Tue Mar 27, 2012 10:25 pm

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.
rudolfo
Volunteer
 
Posts: 1415
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Convert VBA from Excel Application

Postby makuck » Thu May 10, 2012 10:31 pm

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
makuck
 
Posts: 2
Joined: Thu May 10, 2012 10:27 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests