One rather old application I’ve supported for several years loads data from Excel spreadsheets into a reporting database. These Excel spreadsheets have always been manually updated by several users. However, because the data that the users are entering into these spreadsheets are first being entered into another separate application database, these users have been doing double-entry – a redundant process which can be easily remedied by various means.
Ideally, the solution for this problem would be to extract the data from the application database and load it into the reporting database using an SSIS package. Unfortunately, that would require some redevelopment of the application which loads data into the reporting database, and we (and the customers) have no bandwidth for that. So I came up with a quick workaround that made everyone happy – using a VBA macro to automatically populate the spreadsheets with data when the users open them.
The tricky part here was getting Excel to connect to Oracle with the least amount of work having to be done on the users’ PCs, which are remote from my location.
First of all, since these users don’t require SQL Plus or any development tools for Oracle, the full client software was unnecessary. Also, the Oracle Instant Client could be pushed to the users with Altiris Deployment Solution.
I had the Instant Client software installed on the PCs, then I added the requisite database connection information to the tnsnames.ora file.
Nota bene: In the Instant Client (or at least in our setup, using version 11.2.0.4) the tnsnames file is in
C:\oracle\instantclient_11_2_0_4 rather than in C:\oracle\product\11.2.0.4\client_1\NETWORK\ADMIN as it often would be in the full Oracle client.
The connection in VBA was simple enough, but not immediately obvious – notice that the connection string includes “Microsoft ODBC Driver for Oracle” rather than an Oracle driver; even though this is used, no ODBC connection needs to be set up in the ODBC Data Source Administrator. It is only imperative that the proper entries exist in the tnsnames.ora file, and that the Microsoft ActiveX Data Object Library is installed and referenced in Excel. (Add References by navigating to Tools –> References in the VBA editor in Excel.)
In a subroutine, this code was used to connect to the database and pull data.
Dim SQL_String As String
Dim dbConnectStr As String
Dim con As New ADODB.Connection
Dim recset As New ADODB.Recordset
Dim strUid As String
Dim strPwd As String
Dim strEnv As String
Dim strDSN As String
Dim iRow As Integer
strEnv = "prod"
strUid = "username"
strPwd = "password"
If strEnv = "prod" Then
strDSN = "(prod database net_service_name* from tnsnames)"
Else
strDSN = "(dev database net_service_name* from tnsnames)"
End If
dbConnectStr = "Driver={Microsoft ODBC for Oracle}; " & _
"Server=" & strDSN & ";" & _
"uid=" & strUid & ";pwd=" & strPwd & ";"
con.ConnectionString = dbConnectStr
con.Open
SQL_String = "(insert SQL query here)"
recset.Open SQL_String, con
iRow = 0
Do While Not recset.EOF
'Have a loop here to go through all the fields
Sheet1.Range("A" & iRow).Value = recset.Fields("colname") ' colname = Column Name from SQL query
' &c. ...
iRow = iRow + 1
recset.MoveNext
Loop
recset.Close
con.Close