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

7 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:

http://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

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




Follow

Get every new post delivered to your Inbox.

Join 144 other followers

%d bloggers like this: