KnowledgeBase 00070: Populating an Excel Spreadsheet from QM

Last updated: 22 Jul 2016 Applies to: All versions

Search   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.Clear 
   
   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("127.0.0.1", -1, "username", "password", "demo") Then 
      fno = QMOpen("STOCK") 
      If fno > 0 Then 
         rec = QMExecute("SSELECT STOCK TO 1", err) 
         Do 
            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 
         Loop 
         
      End If 
      
      Sheet1.Columns("A:D").AutoFit 
      
      QMDisconnect 
   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

None.

Please tell us if this article was helpful

Very     Slightly     Not at all

Comments

Email (optional)