Select From or Update a Database Table Based on the Contents of an Excel Spreadsheet

12 01 2010

January 12, 2010

Let’s say that there is an Excel spreadsheet containing a list of customer order IDs in column A, and you would like to query an Oracle database using the value in column A, and then display a message on the screen showing the results of the query.  The following macro code will do just that:

Sub CheckSpreadsheet() 
    Dim dbMyDB As New ADODB.Connection 
    Dim snpData As New ADODB.Recordset 
    Dim intLastRowChecked 
    Dim intFoundFirstBlank 
    Dim intResult As Integer 
    Dim intColumn 
    Dim strColumn 
    Dim strFilename 
    Dim strWorkbookname 
    Dim strSheet 
    Dim strExcelValue 
    Dim strSQL 
    Dim strMessage
    'You must create a reference to Microsoft ActiveX Data Objects (Tools menu)
    'Make sure that we don't crash - will look ugly if our macro crashes
    On Error Resume Next
    'Replace MyODBCConnection with an ODBC connection name, MyUserName with a database user name and MyPassword with the user's password 
    dbMyDB.ConnectionString = "Data Source=MyODBCConnection;User ID=MyUserName;Password=MyPassword;"
    dbMyDB.ConnectionTimeout = 40 
    dbMyDB.CursorLocation = adUseClient 
    dbMyDB.Open
    strWorkbookname = Right(ActiveWorkbook.FullName, Len(ActiveWorkbook.FullName) -InStrRev(ActiveWorkbook.FullName, "\")) 
    strSheet = ActiveSheet.Name
    intLastRowChecked = 1 'Set to skip the first row 
    intColumn = 65  'Column A 
    strColumn = Chr(intColumn)
    Do While intFoundFirstBlank = False 
        intLastRowChecked = intLastRowChecked + 1
        'Read the value from the spreadsheet 
        strExcelValue = Format(Workbooks(strWorkbookname).Worksheets(strSheet).Range(strColumn & Format(intLastRowChecked)).Value)
        If strExcelValue = "" Then 
            intFoundFirstBlank = True 
        Else 
            'Could perform an INSERT statement rather than a SELECT statement 
            strSQL = "SELECT" & vbCrLf 
            strSQL = strSQL & "  LINE_NO," & vbCrLf 
            strSQL = strSQL & "  PART_ID," & vbCrLf 
            strSQL = strSQL & "  ORDER_QTY," & vbCrLf 
            strSQL = strSQL & "  DESIRED_SHIP_DATE" & vbCrLf 
            strSQL = strSQL & "FROM" & vbCrLf 
            strSQL = strSQL & "  CUST_ORDER_LINE" & vbCrLf 
            strSQL = strSQL & "WHERE" & vbCrLf 
            strSQL = strSQL & "  CUST_ORDER_ID='" & strExcelValue & "'" & vbCrLf 
            strSQL = strSQL & "ORDER BY" & vbCrLf 
            strSQL = strSQL & "  DESIRED_SHIP_DATE"
            snpData.Open strSQL, dbMyDB
            Do While Not snpData.EOF 
                strMessage = strExcelValue & "/" & Format(snpData("line_no")) & " " & Format(snpData("desired_ship_date"), "m/d/yyyy") & _
                             " " & snpData("part_id") & " Qty " & Format(snpData("order_qty")) 
                MsgBox strMessage
                snpData.MoveNext 
            Loop 
            snpData.Close 
        End If 
    Loop
    Set snpData = Nothing 
    dbMyDB.Close 
    Set dbMyDB = Nothing 
End Sub

Note that there are a couple minor issues with the above script:

  • The script runs until it finds a blank cell in column A, rather than using an Excel feature to identify the bounds of the range.
  • The script requires an ODBC (32 bit) to be created on the computer.  Search the other articles on this blog to see how to establish a connection to the database without creating an ODBC connection.
  • The script does not use bind variables.  Search the other articles on this blog to see how to implement bind variables in an Excel macro.

By changing the script slightly, the SELECT statement could be modified to be an UPDATE statement, allowing an easy method to update the database based on data contained in the Excel spreadsheet.


Actions

Information

9 responses

13 01 2010
Greg

Hi,
what do You mean by ‘You must create a reference to Microsoft ActiveX Data Objects ‘ ?

13 01 2010
Charles Hooper

See the example of adding a reference to the Microsoft ActiveX Data Objects in this blog article:
https://hoopercharles.wordpress.com/2009/12/28/extract-1834-images-from-excel-2007-and-transfer-to-a-database-table/

14 01 2010
Greg

Got it, thanks .
Regards
Greg

22 01 2010
Ben Weiss

Hi Charles – Great blog! You have a wide ranging mix of Oracle topics, and it’s cool to see some VBA thrown in. I wanted to share with you my own Excel VBA tool that queries Oracle, SQL Server and MS-Access and enables end users to add their own queries. Go to http://dbbulletin.wordpress.com/downloads and take a look at the “Data Getter” section.
cheers,
Ben

22 01 2010
Charles Hooper

Ben, thanks for the compliment regarding the contents of the blog – some of the articles required a significant amount of time to put together. It is good to hear that people are finding some of the articles to be interesting.

13 10 2012
Tony

Hi Charles,
Can the above also be used to update supplier delivery dates. For example if i send out a blanket purchase order to a supplier based on an annual quantity of 52K and call off quantities of 1k a week, there may be weeks when i need more. Oracle would then request me to pull some delivery dates forward. I can export these pull forwards into excel. When the supplier replies with the new delivery dates in Excel can I upload these new dates ?

It would save a lot of time rather than going into Oracle and having to change each line manually. Particularly when you have to update over 100 lines.

14 10 2012
Charles Hooper

Tony,

I do not see why you could not use a variation of my macro to do what you describe. However, it makes sense to first try tracing (with a 10046 level 4 trace) what the program does when the supplier delivery dates are modified through the application interface. You only need to be concerned with those SQL statements that are indicated with dep=0. There is a chance that the application interface also updates rows in other tables when the dates are changed.

Note that the macro in this article moves down column A in a worksheet, stopping when the first blank cell in column A is found. So, what if you need the macro to continue searching through the rows until the last row in the worksheet is reached (skipping those rows with a blank in the searched column)? You can modify the macro similar to the following – note that cell A1 must contain something, even a blank space, for the macro to work correctly, so that is why the macro checks that cell. Notice that this macro references the cells using row, column syntax rather than D1, D2, D3, etc. syntax:

Sub CheckSpreadsheet()
    Dim lngRows As Long
    Dim lngCols As Long
    
    Dim i As Long
    Dim j As Long
    
    Dim strExcelValue As String
    Dim strSQL As String
    
    Dim dbMyDB As New ADODB.Connection
    Dim snpData As New ADODB.Recordset
    
    'You must create a reference to Microsoft ActiveX Data Objects (Tools menu)
    'Make sure that we don't crash - will look ugly if our macro crashes
    On Error Resume Next
    'Replace MyODBCConnection with an ODBC connection name, MyUserName with a database user name and MyPassword with the user's password
    dbMyDB.ConnectionString = "Data Source=MyODBCConnection;User ID=MyUserName;Password=MyPassword;"
    dbMyDB.ConnectionTimeout = 40
    dbMyDB.CursorLocation = adUseClient
    dbMyDB.Open

    'Cell A1 (referenced by ActiveSheet.Cells(1, 1)) must not be blank
    If ActiveSheet.Cells(1, 1) = "" Then
        ActiveSheet.Cells(1, 1) = " "
    End If
    
    lngRows = ActiveSheet.UsedRange.Rows.Count
    lngCols = ActiveSheet.UsedRange.Columns.Count
    
    MsgBox "Row Count: " & Format(lngRows)
    MsgBox "Column Count: " & Format(lngCols)
    
    i = 4 'We will check the values in the fourth column (D)
    
    For j = 1 To lngRows
        strExcelValue = Format(ActiveSheet.Cells(j, i).Value)
        If strExcelValue = "" Then
            'There is nothing in this cell, skip the row
        Else
            'Run the SQL statement here
            MsgBox strExcelValue
        End If
    Next j
    
    dbMyDB.Close
    Set snpData = Nothing
    Set dbMyDB = Nothing
End Sub
5 12 2017
Themba

Hi there, I have tried all these but still cant connect. This is what I would use on SAS but now want to use on VBA

%LET HOST1=WSBR2PSAS01;
OPTIONS REMOTE=HOST1.__7551 COMAMID=TCP dlcreatedir;
SIGNON;
rsubmit;
%let user=User; %let pass=”pass”; %let path=”PMIS1″;
Options nosyntaxcheck compress=yes dlcreatedir;
%sysrput pathtask1=%sysfunc(pathname(work));
libname workserv “&pathtask1”;
endrsubmit;
%syslput pathtask1 = &pathtask1;
libname workserv “&pathtask1” server = host1.__7551;

6 12 2017
Charles Hooper

Themba,
I am not familiar with SAS, but I did take a few minutes to check with Google. I found the following article that describes how to use SAS with an Oracle database:
http://support.sas.com/documentation/cdl/en/edmag/70007/HTML/default/viewer.htm#n19rz66xhi8ebvn166bdx2k3uxrj.htm

In that article it is mentioned that SAS uses a JDBC connection to the database, and there is an example of a tnsnames.ora file in that page. For the above method in my article to work, you must have the Oracle Client software installed on your computer, with the ODBC drivers selected to be installed with the Oracle Client. If you are using the 32 bit version of Excel, you must have the 32 bit Oracle Client installed. Also, if you are using the 32 bit version of Excel, this example requires that you create a 32 bit ODBC connection item on the computer.

If you do not want to create a 32 bit ODBC connection item on the computer, then the other option is to install the OLEDB drivers with the Oracle Client and use the connection method that is demonstrated in the following article (in this case, you would use the database name that appears in the tnsnames.ora file shown as monitordb in the above SAS article):
https://hoopercharles.wordpress.com/2010/10/30/ms-query-teases-you-excel-will-not-display-text-contained-in-long-rawblob-column/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: