|Powered by QM on a Rpi server|
KnowledgeBase 00070: Populating an Excel Spreadsheet from QM
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 SubFor 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