Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert Excel VBA to OpenOffice Calc VB calling COM DLL

Posted on 2015-01-17
14
Medium Priority
?
320 Views
Last Modified: 2015-01-20
I am trying to call a COM DLL in OpenOffice Calc. This is how it is done in Excel:

Public Function IDAutomation_QRFontEncoder(DataToEncode As String) As String
    Dim QRFontEncoder As QRCode
    Set QRFontEncoder = New QRCode
    QRFontEncoder.FontEncode DataToEncode, 0, 0, 0, 0, IDAutomation_QRFontEncoder
End Function

How can I do the same in Calc?
I am using this product:
http://www.idautomation.com/barcode-fonts/2d/qr-code/user-manual.html#COM_DLL_font_encoder
For testing, you could download the demo here:
http://downloads.idautomation.com/IDAutomation_QRCodeFontEncoderDEMO.zip
0
Comment
Question by:tncode
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
14 Comments
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558424
Hi tncode,

In OpenOffice Calc Basic you can create an instance of a COM component by:

oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )

so, without knowing too much about QRCode, you should be able to achieve what you are looking to do by coding the following into a Macro.

oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
QRFontEncoder.FontEncode(DataToEncode, 0, 0, 0, 0, IDAutomation_QRFontEncoder)

As I don't have QRCode installed I am guessing a bit about the name of the component in the above code using an installation of IDAutomation's BarCode demo. If the ProgID "IDAuto.QRCode" isn't correct then you will get a "Variable not set error" when you attempt to call a method or read a property. In this case searching the Registry hive HKEY_CLASSES_ROOT using the search term QRCode should help identify what the correct ProgID is.

A bit of tinkering around with the above code should get you to where you want to be.

Hope this helps.
0
 
LVL 6

Author Comment

by:tncode
ID: 40558564
I have verified that the ProgID is IDAuto.QRCode. However, I am still getting "basic runtime error; Object variable not set". Is there anything else I can do? This is my function:

Public Function IDAutomation_QRFontEncoder(DataToEncode As String) As String
      oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
      oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
      QRFontEncoder.FontEncode(DataToEncode, 0, 0, 0, 0, IDAutomation_QRFontEncoder)
End Function
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558567
tncode,

function IDAutomation_QRFontEncoder(DataToEncode As String, ApplyTilde As Long, EncodingMode As Long, Version As Long, ErrorCorrectionLevel As Long)

oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
oQRFontEncoder.FontEncode(DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel, IDAutomation_QRFontEncoder)

end function

and call function:

retval = IDAutomation_QRFontEncoder("SOMEDATA", 0, 0, 0, 0)
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558573
BTW the ProgID is correct, I downloaded the Demo and installed it. The code above runs; but appears to return an empty string?
0
 
LVL 6

Author Comment

by:tncode
ID: 40558611
I am able to get the code error free now after changing "QRFontEncoder" to "oQRFontEncoder". However, the function does not return any data. It does return a text string in Excel. Do you know what could be causing this?

Public Function IDAutomation_QRFontEncoder(DataToEncode As String) As String
      oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
      oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
      oQRFontEncoder.FontEncode(DataToEncode, 0, 0, 0, 0, IDAutomation_QRFontEncoder)
End Function
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558622
tncode,

IDAutomation's own documentation appears to be incorrect. The first input parameter to the FontEncode method gets changed by the call; so:

function IDAutomation_QRFontEncoder(DataToEncode As String, ApplyTilde As Long, EncodingMode As Long, Version As Long, ErrorCorrectionLevel As Long)
oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
oQRFontEncoder.FontEncode DataToEncode, ApplyTilde, EncodingMode, Version, ErrorCorrectionLevel, IDAutomation_QRFontEncoder
IDAutomation_QRFontEncoder = DataToEncode
end function

calling with:

dim barcode
barcode = "EXAMPLE1"
Print IDAutomation_QRFontEncoder(barcode, 0, 0, 0, 0)
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558658
... and i'm assuming that this string needs to be printed using ID's Barcode Font??
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40558870
... or maybe it's just the way that OpenOffice implementation of Basic works. Either way, my last posting of code works for me; which iv'e tested using ID Automations' Demo Application, copying the converted barcode string to the clipboard, pasting this into notepad, and changing the font to IdAutomation. The text string displayed was:

AHEEEHAPGOANAPAHEEEHA
BNFFFNBPAPBKEPBNFFFNB
FLMIFKFHDDIBLEFGFDLJD
MNFNNFEHJJMOAEFONLHIC
APBBBPAPHOPFLBFNEKFDL
HHHHHHHPHPPPHPPHHPHPH

... and when this is formatted using the IDAutomation font, it displays the same 2D barcode as the demo application.
0
 
LVL 6

Author Comment

by:tncode
ID: 40560025
I still can't get it working in Calc. Hmmm, strange. Attached is a sample file I have been working with. At this point I am trying to display the result as text.
I am calling this VB macro from cell B2: =IDAUTOMATION_QRFONTENCODER(A2)
The macro is in basic within "ThisWorkbook". I have uploaded IDauto-Calc-Example.zip if you don't mind looking at it?
IDauto-Calc-Example.zip
0
 
LVL 13

Accepted Solution

by:
John Mc Hale earned 2000 total points
ID: 40560847
Hi tncode,

As I explained in my last postings, the call to the FontEncode method of the QRCode object actually alters the first input parameter, returning the encoded barcode text string in the same parameter. I don't know what the difference between Microsoft Excel and OpenOffce Calc is in this regard. The correct code for the call in OpenOfffce Calc is:

Public Function IDAutomation_QRFontEncoder(DataToEncode As String) As String
      dim DummyRetVal as string
      oObjFactory = createUnoService( "com.sun.star.bridge.OleObjectFactory" )
      oQRFontEncoder = oObjFactory.createInstance("IDAuto.QRCode")
      oQRFontEncoder.FontEncode(DataToEncode, 0, 0, 0, 0, DummyRetVal)
      IDAutomation_QRFontEncoder = DataToEncode
End Function


Note my use of the "DummyRetVal" variable to illustrate that this value DOES NOT APPEAR TO BE ALTERED by the call to the FontEncode method.

I have some minor changes to your spreadsheet attached.

Best Regards,

John.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40560868
Sorry tncode... attaching file.

IDauto-Calc-Example.zip
0
 
LVL 6

Author Comment

by:tncode
ID: 40560907
Oh wow, its working!   Thanks a bunch, I had no idea it would work like that :)
0
 
LVL 6

Author Closing Comment

by:tncode
ID: 40560908
This guy really understands COM and Office.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 40560922
Hi tncode,

Glad I could help and thanks for the points... it's been a while!

Best Regards,

John.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question