Calling QMClient from 64-bit Excel

[Brian Speirs]

Hi,

I've had a VBA library for use with Excel based on the article in the knowledgebase (http://portal.openqm.com/?T0=h&X=0pv5up8ix7&t1=kb.00070) which has worked fine for some years.

However, a recent upgrade has moved Excel to a 64-bit application ... with the result that use of the 32-bit QMClient.dll fails.

It isn't clear to me from the documentation which client library I should now use. For example, the documentation says to use qmclivb.dll AND qmclilib.dll for VB.Net. Are these the appropriate libraries to use with VBA7? And how do we reference TWO dlls within the VBA code?

Can anyone help?

Thanks,

Brian

[Kevin Powick]

Hi Brian,

There are two things you should do.   Update your VBA macros to detect 32/64 bit versions of MS Office, and ensure you have the correct QM DLLs available.  Details below.

1)  Update your VBA "macros" to determine which version of Office you're running so that you can use the correct calling convention.  You can do this by using the the compilation constants #VBA7 or #Win64.  Note that there is a subtle difference between what these two constants represent.

https://docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2010/ff700513(v=office.11)

Simplified VBA Excel macro in a module's "Declarations" section.  The main difference is the calling convention for QM functions.

'Define variables
#If VBA7 Then
    Declare PtrSafe Function QMConnect Lib "QMClient.dll" (ByVal Host As String, ByVal Port As Integer, ByVal UserName As String, ByVal Password As String, ByVal Account As String) As Boolean
    Declare PtrSafe Function QMDcount Lib "QMClient.dll" (ByVal Src As String, ByVal Delim As String) As Long
    Declare PtrSafe Function QMExtract Lib "QMClient.dll" (ByVal Src As String, ByVal fno As Integer, ByVal Vno As Integer, ByVal Svno As Integer) As String
#Else
    Declare Function QMConnect Lib "QMClient.dll" (ByVal Host As String, ByVal Port As Integer, ByVal UserName As String, ByVal Password As String, ByVal Account As String) As Boolean
    Declare Function QMDcount Lib "QMClient.dll" (ByVal Src As String, ByVal Delim As String) As Long
    Declare Function QMExtract Lib "QMClient.dll" (ByVal Src As String, ByVal fno As Integer, ByVal Vno As Integer, ByVal Svno As Integer) As String
#End If

2) On 32-bit machines, you can only have 32 bit versions of Excel and QM, so it's straightforward which QM Dlls are required.  However, you can run the 32-bit version of Excel on 64-bit Windows, so you'll probably want to make sure that your 64-bit QM installations have both 32-bit and 64 bit QMClient Dlls available. 

On 32-bit machines, I have the 32-bit version of qmclient.dll and qmclilib.dll in Windows\System32

On 64-bit machines, I have the 64-bit versions of qmclient.dll and qmclilib.dll in Windows\System32, and I have the 32-bit versions of those files in Windows\SysWOW64.

--

Kevin Powick

[Brian Speirs]

Thanks, Kevin. I actually had most (all) of that, but when it didn't work I ran way off course. In the end, it turned out to be a self-inflicted problem:

I had defined CLIPORT (in qmconfig) as 44243 which is fine for a C connection where you can use unsigned integers, but VBA uses signed integers and 44243 was not a valid integer value. Setting CLIPORT to a (positive) value in the signed integer value range solved the problem.

Theoretically, I should be able to pass a long integer value in the unsigned integer range, and convert that to an equivalent signed integer; then use that value in the connection. But my quick dabble with that didn't work - so I'll stick to the values that DO work.

Thanks,

Brian