Home : Products : Hach WIMS : Custom Toolbox : Using Excel VBA/ADO to read WIMS data
Q13473 - INFO: Using Excel VBA/ADO to read WIMS data

The following example uses Excel to access the WIMS database (MS SQL 2005) via Microsoft VB 6.0(or VBA) and ADO (ActiveX Data Objects).  All examples are in VB 6.0.

NOTE: You should be familiar with VB and basic programming techniques to use this section.  HACH offers NO support for any custom application and information provided here is intended for programmers.

Step 1:  Add a user/login into MS SQL Server for use in reading data.  We sugguest you add the Login name "WIMSRO" with a password of READONLY with data reader access to all your WIMS Facility databases:

 

 

Step 2: In the Excel Visual Basic Developer add a reference to ADO.  Go to Tools, References and add a reference to Microsoft ActiveX Data Objects 2.7 Library:

 

Step 3:  Paste the following code into a new VB Module.  Edit the DB_LOGIN information highlighted:


'===========================================================================================================
'DISCLAIMER : Use this code at your own risk. HACH is not liable for any damage caused by execution
'             of this code and/or any modified version of it.
'===========================================================================================================


'===========================================================================================================
'NOTES:       This code snippet in its original form has been tested to be safe and working. However, it
'             has not been in any way optimized for speed and server resource utilization.
'===========================================================================================================


'===========================================================================================================
'local constants - these must be configured to match your SQL server environment
 Const DB_SERVER_NAME As String = "(local)\OPSSQL"                    'hold the name of your SQL 2000 or later server
 Const DB_LOGIN_USER As String = "WIMSRO"                'holds the login name of your user
 Const DB_LOGIN_PASSWORD As String = "READONLY"              'holds the login password of of your user
 Const DB_LOGIN_DATABASE As String = "OPSWWSALES"              'holds the database name to connect to

Dim cnn As New ADODB.Connection
'===========================================================================================================

 

'===========================================================================================================
' GetSQL_RawValue
' DESC  : function returns a value of an OPS SQL datapoint uniquely identified by its VARID and DATESTAMP
' INPUT : VarId = Variable ID, Currdate = DATESTAMP
' OUTPUT: If a value is found it is returned. Otherwise a blank string is returned
'===========================================================================================================
Function GetSQL_RawValue(VarId As Long, CurrDate As Date)
    'declare local varibales
   
    Dim rs As New ADODB.Recordset
    Dim source_table As String
    Dim date_string As String
    Dim SQL As String
   
    On Error GoTo errors_happen:
   
    'Exit Function
    If cnn.State <> 1 Then
        'setup a database connection
        cnn.ConnectionString = "Provider=SQLNCLI;"                                                 'driver name SQLOLEDB for 2000
        cnn.ConnectionString = cnn.ConnectionString & "Data Source=" & DB_SERVER_NAME               'server name
        cnn.ConnectionString = cnn.ConnectionString & ";initial catalog=" & DB_LOGIN_DATABASE       'database name
        cnn.ConnectionString = cnn.ConnectionString & ";User Id=" & DB_LOGIN_USER                   'login as
        cnn.ConnectionString = cnn.ConnectionString & ";Password=" & DB_LOGIN_PASSWORD              'login password
        cnn.ConnectionString = cnn.ConnectionString & ";OLE DB Services =-2;"
        cnn.CursorLocation = adUseClient
   
        'attempt to establish database connection
        cnn.Open
    End If
    'set the cursor location
   
   
    'set the source table for records. Remember that different variable frequencies are stored in different tables
    'also make sure that your logged in MSSQL user has been granted SELECT priviledges on the particular table
    source_table = "DATATBL"
   
    'translate the date argument into an a valid SQL syntax
    date_string = SQLDate(CurrDate)
   
    'generate SQL query
    SQL = "SELECT CURVALUE FROM DBO." & source_table & " WHERE VARID = " & VarId & " AND DATESTAMP = '" & date_string & "'"
   
    'run the query against the open connection
    rs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
   
    If Not rs.EOF Then
        GetSQL_RawValue = rs(0) 'query returned a match
    Else
        GetSQL_RawValue = ""    'query returned no match
    End If
   
    'close connection
    rs.Close
    'cnn.Close
   
    'return control to the parent procedure
    Exit Function
   
errors_happen:
    'catch errors that may occur
   
    'generate error message text
    msg$ = "Error Occured in function GetSQL_RawValue" & Chr(13) & Chr(10)
    msg$ = msg$ & "Details : " & cnn.Errors.Item(0).Number & " - " & cnn.Errors.Item(0).Description
                       
    'display error message
    MsgBox msg$, vbExclamation + vbOKOnly
    GetSQL_RawValue = ""
End Function


'===========================================================================================================
' GetSQL
' DESC  : function returns a value that represents an agregate result of a query for a particular variable
'         id in a specified date-range
' INPUT : VarId = Variable ID, StartDate = Start DATESTAMP, EndDate = End DATESTAMP
'         Stat = AGREGATE STATISTIC {MAX,MIN,AVG,SUM, etc}
' OUTPUT: If a value is found it is returned. Otherwise a blank string is returned
'===========================================================================================================

Function GetSQL(ByVal VarId As Long, ByVal Stat As String, ByVal StartDate As Date, ByVal EndDate As Date)
    'declare local varibales
    'Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim source_table As String
    Dim date_string As String
    Dim date_string2 As String
    Dim SQL As String
   
    On Error GoTo errors_happen:
   
    If cnn.State <> 1 Then
        'setup a database connection
        cnn.ConnectionString = "Provider=SQLOLEDB;"                                                 'driver name
        cnn.ConnectionString = cnn.ConnectionString & "Data Source=" & DB_SERVER_NAME               'server name
        cnn.ConnectionString = cnn.ConnectionString & ";initial catalog=" & DB_LOGIN_DATABASE       'database name
        cnn.ConnectionString = cnn.ConnectionString & ";User Id=" & DB_LOGIN_USER                   'login as
        cnn.ConnectionString = cnn.ConnectionString & ";Password=" & DB_LOGIN_PASSWORD              'login password
        cnn.ConnectionString = cnn.ConnectionString & ";OLE DB Services =-2;"
        'set the cursor location
        cnn.CursorLocation = adUseClient
   
        'attempt to establish database connection
        cnn.Open
    End If
   
   
    'set the source table for records. Remember that different variable frequencies are stored in different tables
    'also make sure that your logged in MSSQL user has been granted SELECT priviledges on the particular table
    source_table = "DATATBL"
   
    'translate the date arguments into an a valid SQL syntax
    date_string = SQLDate(StartDate)
    date_string2 = SQLDate(EndDate)
   
    'generate SQL query
    SQL = "SELECT " & Stat & "(CURVALUE) FROM DBO." & source_table & " WHERE VARID = " & VarId & " AND DATESTAMP >= '" & date_string & "' AND DATESTAMP <'" & date_string2 & "'"
   
    'run the query against the open connection
    rs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
   
    If Not rs.EOF Then
        GetSQL = rs(0) 'query returned a value
    Else
        GetSQL = ""    'query returned no match
    End If
   
    'close connection
    rs.Close
    'cnn.Close
   
    'return control to the parent procedure
    Exit Function
   
errors_happen:
    'catch errors that may occur
   
    'generate error message text
    msg$ = "Error Occured in function GetSQL" & Chr(13) & Chr(10)
    msg$ = msg$ & "Details : " & cnn.Errors.Item(0).Number & " - " & cnn.Errors.Item(0).Description
                       
    'display error message
    MsgBox msg$, vbExclamation + vbOKOnly
    GetSQL = ""

End Function

 

'===========================================================================================================
'a helper function
Private Function SQLDate(ByVal TempDate As Date) As String
    SQLDate = Format(TempDate, "yyyy-mm-dd")        'MSSQL
End Function
'===========================================================================================================

 

Step 4: Enter the following formula in Excel:

=GETSQL_RawValue(1,"1/1/2009")

The GETSQL_RawValue(1,"1/1/2009") returns the value for the VARID (NOT VARNUM) 1 for the date 1/1/2009.

Step 5: Enter the GETSQL formula in cell B4:

=GETSQL(1,"AVG","1/1/2009","1/15/2009")

 

You have now retrieved data from the WIMS Database into Excel.  You can save your file and expand on it. 

NOTE:  HACH offers NO Support on using these functions.  If you need assistance it will require a services contract. 

 

 

 


 

Related Articles
No Related Articles Available.

Article Attachments
WIMS_Example_DataReader for Microsoft SQL 2005.xls

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 7/11/2011 5:02 PM.
Last Modified on 7/12/2011 8:20 AM.
Last Modified by Scott Dorner.
Article has been viewed 7197 times.
Rated 0 out of 10 based on 0 votes.
Print Article
Email Article