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

12012010

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:

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

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.

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.

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

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: