Powered by QM on a Rpi server
About OpenQM
Sales and Downloads
Help and Support

KnowledgeBase 00070: Populating an Excel Spreadsheet from QM

Last updated: 17 Feb 2017
Applies to: All versions
Top level index       Full Index Search Tips
Previous article     Next article

This article was originally published as a Tip of the Week.

There are several third party tools to create spreadsheets from QM data files. Although they offer interesting features, they are not needed for simple display of data in a spreadsheet. This article shows how to create an Excel spreadsheet from one the the QM demonstration files using an Excel macro.

An Example Macro

This simple VBA macro uses QMClient to read records from the demonstration STOCK file that is created by QM's SETUP.DEMO command.

Private 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 
Private Declare Sub QMDisconnect Lib "QMClient.dll" () 
Private Declare Function QMExecute Lib "QMClient.dll" (ByVal Command As String, _ 
        ByRef err As Integer) As String 
Private Declare Function QMExtract Lib "QMClient.dll" (ByVal rec As String, _ 
        ByVal f As Integer, ByVal v As Integer, ByVal s As Integer) As String 
Private Declare Function QMOpen Lib "QMClient.dll" (ByVal FileName As String) _ 
        As Integer 
Private Declare Function QMReadNext Lib "QMClient.dll" (ByVal ListNo As Integer, _ 
        ByRef err As Integer) As String 
Private Declare Function QMRead Lib "QMClient.dll" (ByVal FileNo As Integer, _ 
        ByVal Id As String, ByRef err As Integer) As String 
Sub QMData() 
   Dim fno As Integer 
   Dim rec As String 
   Dim err As Integer 
   Dim id As String 
   Sheet1.Cells(1, 1).Value = "Id" 
   Sheet1.Cells(1, 2).Value = "Description" 
   Sheet1.Cells(1, 3).Value = "Stock" 
   Sheet1.Cells(1, 4).Value = "Price" 
   Sheet1.Columns(4).NumberFormat = "$#.00" 
   Sheet1.Rows(1).Font.Bold = True 
   Row = 1 
   If QMConnect("", -1, "username", "password", "demo") Then 
      fno = QMOpen("STOCK") 
      If fno > 0 Then 
         rec = QMExecute("SSELECT STOCK TO 1", err) 
            id = QMReadNext(1, err) 
            If err <> 0 Then Exit Do 
            rec = QMRead(fno, id, err) 
            If err = 0 Then 
               Row = Row + 1 
               Cells(Row, 1).Value = id 
               Cells(Row, 2).Value = QMExtract(rec, 1, 0, 0)  ' Description 
               Cells(Row, 3).Value = QMExtract(rec, 2, 0, 0)  ' Quantity 
               Cells(Row, 4).Value = QMExtract(rec, 3, 0, 0) / 100  ' Price 
            End If 
      End If 
   End If 
End Sub 
For an application where the Excel spreadsheet resides on the same system as the QM database, using the QMConnectLocal() function in place of QMConnect() would remove the need to include user authentication data in the macro.

A Generalised Query

By using QMExecute to execute a query processor command that uses the CSV keyword to return its result in comma separated variable format, it is possible to build a macro that populates a spreadsheet with the result of a generalised query. The relevant steps in this process are shown below. Some minor modifications would be needed if the returned data included quoted items.

rec = QMExecute("query command", err) 
NumRows = QMDcount(rec, chr(254)) 
For i = 1 To NumRows 
   s = QMExtract(rec, i, 0, 0) 
   NumCols = QMDcount(s, ",") 
   For j = 1 To NumCols 
      Cells(i,j).Value = QMField(s, ",", j) 
   Next j 
Next i 

Related Articles


Please tell us if this article was helpful
Very     Slightly     Not at all
Email (optional)