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.
Hi,
what do You mean by ‘You must create a reference to Microsoft ActiveX Data Objects ‘ ?
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/
Got it, thanks .
Regards
Greg
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.
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:
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;
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/