Macro (SVB) Program Examples - Running a Query via a Macro

This example will run a specified query (by manipulating STATISTICA Query via a macro) and insert the returned data into a new spreadsheet:

Private Const MyCONNECT_STRING As String = _
"Insert your own custom connection statement here"

Private Const MySQL As String = _
"Insert your own custom query statement here"

Sub Main

On Error GoTo Error_

Dim s As Spreadsheet
s = Spreadsheets.New
qc As Queries
qc = s.Queries
q As Query
q = qc.Add("My query", MyCONNECT_STRING, 1, 1, MySQL)
q.Refresh True

While q.Refreshing

Wait 1


s.Visible = True

Exit Sub


If Err.Number <> 0 Then

Msg = "Error # " & Str(Err.Number) & " was generated by " _
Err.Source & Chr(13) & Err.Description

MsgBox Msg

End If

End Sub

You will need to insert your own connection and query strings into the two global constants MyCONNECT_STRING and MySQL, respectively, before being able to perform this macro. Note that if your connect string contains quotation marks in it, then you will be required to place an additional quotation mark in front of each occurrence; otherwise, an "unexpected text" error message will be displayed. For example:

Extended Properties="DSN=Query;DBQ=C:\DB\My_DataBase.mdb;DriverId=86;"

Would need to be rewritten as:

Extended Properties=""DSN=Query;DBQ=C:\DB\My_DataBase.mdb;DriverId=86;""

Please refer to How to use strings in STATISTICA Visual Basic for additional information regarding string manipulation.