Retrieve Data to Excel with a Macro using ADO

2 12 2009

The following Excel macro shows how to connect to an Oracle database using ADO, create a new worksheet in the current work book, and then fill in the returned data using a slow method (not recommended) and a fast method.

'Need to add a reference to Microsoft ActiveX Data Objects 2.8 Library before starting
'*** This section might be in the top portion of the user form, worksheet code, or module:
'Declare a connection object in the general section to hold the connection to the database
Dim dbVMFG As ADODB.Connection
 
'Declare a set of variables to hold the username and password for the database
Dim strUserName As String
Dim strPassword As String
Dim strDatabase As String
'***
 
Dim i as Integer
Dim intResult As Integer
Dim strSQL as String
Dim lngRow as Long
    
On Error Resume Next
 
strDatabase = "MyDB"
 
strUserName = "MyUser"
strPassword = "MyPassword"
 
'Connect to the database
'Oracle connection string
Set dbVMFG = New ADODB.Connection
dbVMFG.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
 
dbVMFG.ConnectionTimeout = 40
dbVMFG.CursorLocation = adUseClient
dbVMFG.Open
 
If (dbVMFG.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Excel Demo")
Else
    strSQL = "SELECT" & VBCrLf
    strSQL = strSQL & "  ID AS RESOURCE_ID," & VBCrLf
    strSQL = strSQL & "  DESCRIPTION" & VBCrLf
    strSQL = strSQL & "FROM" & VBCrLf
    strSQL = strSQL & "  MY_TABLE" & VBCrLf
    strSQL = strSQL & "WHERE" & VBCrLf
    strSQL = strSQL & "  DESCRIPTION LIKE '%10%'" & VBCrLf
    strSQL = strSQL & "ORDER BY" & VBCrLf
    strSQL = strSQL & "  ID"
 
    'Add a new worksheet to the new workbook, add after the last sheet
    ActiveWorkbook.Sheets.Add
    ActiveWorkbook.ActiveSheet.Name = "JustATest"
        
    snpData.Open strSQL, dbVMFG
        
    If snpData.State = 1 Then
        'Slow Method------------------------------------
        lngRow = 0
        'Header Row
        For i = 0 To snpData.Fields.Count – 1
            lngRow = lngRow + 1
            ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i).Name
            ActiveSheet.Cells(lngRow, i + 1).Font.Bold = True
        Next i
 
        'Detail Rows
        Do While Not snpData.EOF
            lngRow = lngRow + 1
            For i = 0 To snpData.Fields.Count - 1
                ActiveSheet.Cells(lngRow, i + 1).Value = snpData.Fields(i)
            Next i
 
            snpData.MoveNext
        Loop
        'End Slow Method------------------------------------
 
        'Fast Method----------------------------------------
        'Do not use this and the slow method!
        For i = 0 To snpData.Fields.Count - 1
            ActiveSheet.Cells(1, i + 1).Value = snpData.Fields(i).Name
        Next i
        ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(1, snpData.Fields.Count)).Font.Bold = True
    
        ActiveSheet.Range("A2").CopyFromRecordset snpData
            
        'Auto-fit up to 26 columns
        ActiveSheet.Columns("A:" & Chr(64 + snpData.Fields.Count)).AutoFit
        'End Fast Method----------------------------------------
 
        snpData.Close
    End If
End If

Actions

Information

18 responses

12 12 2009
Steve Howard

Finally! Someone else who uses Excel to query an Oracle database :)

I have used Excel for this purpose since 1999, and constantly have colleagues trying to get me to change to use SQL Developer, TOAD, etc. While each has its merits (and I am sure functionality mine does not), I have found the ability to customize Excel to do exactly what I want always outweighs any loss in functionality (which normally isn’t that important to me anyway).

It’s name? SHAM. Steve Howard Application Manager. :)

12 12 2009
Charles Hooper

It is good to know that there is interest in blog articles related to using Excel for database access beyond what the Microsoft Query tool is able to provide. I agree that Excel is extremely flexible. Six months ago I demonstrated to a couple of regional user’s groups for an ERP package how to build things in Excel to extend the ERP system.

17 01 2010
Greg

I’ve just started with Excel + Oracle combo.
Please tell me how strDatabase = “MyDB” is resolved ?
I’m using OID resloving method and that seems to not working with ADO.
Probably tnsnames file is what ADO needs ? Right ?
Regards.
Greg

17 01 2010
Charles Hooper

Yes, that particular example (and most of the examples involving either Excel or a VBS script) uses the identifier from tnsnames.ora.

If you want to manually set up an ODBC connection and use that instead, see this blog article for the connection string:

http://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/

11 05 2010
Mark Steffen

Where is snpData being Dim’d? Also, is there a corresponding reference I should make?

Thanks,
Mark

11 05 2010
Charles Hooper

Mark, nice catch. The code is missing the following two lines:

Dim snpData As ADODB.Recordset
Set snpData = New ADODB.Recordset

The above two lines could be combined into a single line like this:

Dim snpData As New ADODB.Recordset

However, it is my understanding that the above shortcut potentially leads to performance problems. If I recall correctly, ADO must repeatedly test whether or not snpData has been previously used, and therefore the object was previously created, every time it is accessed. The two line approach avoids this extra test.

Make certain that you add a reference to “Microsoft ActiveX Data Objects” in the Visual Basic References window, as stated in the above code.

7 03 2011
Gary Evans

Hi, this is interesting, the Excel collective I mean, and you may all be able to save me here. I’m a first time poster so any rules I have messed up please let me know where I should be post so I can.. I have been totally gazumped and really need support in some fashion.

I have an oracle database, and BIG (350 lines) query that has two WITH tables (one using the other) followed by 26 subsequent UNION ALL select statements that draw their data from the resultant WITH table. This runs fine in SQL developer tool. but then via the ADO (2.8) control I get nothing but zeros for all except the last union select statement

7 03 2011
Charles Hooper

Hi Gary,

That is an interesting problem, a couple of thoughts:
* Are you using a “Provider=OraOLEDB.Oracle” in the ConnectionString? Please post the entire connection string (make certain that you replace the database username and password).
* When you establish the connection to the database, do you configure the connection to use client-side cursors? This is done with a command like this:

dbDatabase.CursorLocation = adUseClient

* ODBC connections can possibly re-write SQL statements before submitting those SQL statements to the database – the database may see an entirely different SQL statement. You can confirm that this is happening by enabling a 10046 trace for the session before submitting the SQL statement. A trace file will be created on the server that shows the exact SQL statement that is submitted. If I recall correctly, ODBC supports a “pass-through” option that does not allow the SQL statement to be rewritten before sending to the database – but that might be a DAO only concept, while the example on this page uses the ADO.
* What version of ADO did you reference in the project? You should be using at least ADO 2.8, which ships pre-installed on computers running Windows XP and later.
* What is the release version of the Oracle client? If you do not know, you can find out by opening a Windows command prompt and type:

tnsping

* What is the release version of the Oracle Database? You can determine the Oracle Database release version by connecting to the database using SQL*Plus (or another tool) and issuing the following SQL statement:

SELECT * FROM V$VERSION;

* Is it possible that the data types differ slightly between the different UNION ALL sections?

7 03 2011
Charles Hooper

Let’s try a simple test case to see if we are able to find the cause of the problem. We will start by creating a simple table to 1,000 rows:

CREATE TABLE T1 AS
SELECT
  ROWNUM C1,
  TRUNC(SYSDATE+ROWNUM-1) C2,
  TO_CHAR(SYSDATE+ROWNUM-1,'DAY') C3
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

A SQL statement with a large number of UNION ALL statements will be used in the Excel program:

SELECT * FROM T1 WHERE C1 BETWEEN 1 AND 20
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 21 AND 40
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 41 AND 60
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 61 AND 80
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 81 AND 100
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 101 AND 120
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 121 AND 140
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 141 AND 160
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 161 AND 180
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 181 AND 200
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 201 AND 220
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 221 AND 240
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 241 AND 260
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 261 AND 280
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 281 AND 300
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 301 AND 320
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 321 AND 340
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 341 AND 360
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 361 AND 380
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 381 AND 400
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 401 AND 420
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 421 AND 440
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 441 AND 460
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 461 AND 480
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 481 AND 500
UNION ALL
SELECT * FROM T1 WHERE C1 BETWEEN 501 AND 520;

In Excel’s programming code editor, create a reference to “Microsoft ActiveX Data Objects 2.8 Library” (see the directions in this article, if necessary: http://hoopercharles.wordpress.com/2011/02/28/oracle-database-time-model-viewer-in-excel-1/ ). Add the following code in the code editor:

Private Sub TestUnion ()
    Dim dbDatabase As ADODB.Connection
    Dim snpData As ADODB.Recordset
    Dim strUsername As String
    Dim strPassword As String
    Dim strDatabase As String
    Dim strSQL As String
    Dim intResult As Integer
    
    strDatabase = "MyDB"
    strUsername = "MyUser"
    strPassword = "MyPassword"

    Set dbDatabase = New ADODB.Connection
    Set snpData = New ADODB.Recordset
    
    dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase _
        & ";User ID=" & strUsername & ";Password=" & strPassword & ";ChunkSize=1000;FetchSize=100;"
        
    dbDatabase.ConnectionTimeout = 40
    dbDatabase.CursorLocation = adUseClient
    dbDatabase.Open
    
    If (dbDatabase.State  1) Or (Err  0) Then
        intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbCrLf & Error(Err), 16, "Test")
        Exit Sub
    End If
    
    strSQL = "SELECT * FROM T1 WHERE C1 BETWEEN 1 AND 20" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 21 AND 40" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 41 AND 60" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 61 AND 80" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 81 AND 100" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 101 AND 120" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 121 AND 140" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 141 AND 160" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 161 AND 180" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 181 AND 200" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 201 AND 220" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 221 AND 240" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 241 AND 260" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 261 AND 280" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 281 AND 300" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 301 AND 320" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 321 AND 340" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 341 AND 360" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 361 AND 380" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 381 AND 400" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 401 AND 420" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 421 AND 440" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 441 AND 460" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 461 AND 480" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 481 AND 500" & vbCrLf
    strSQL = strSQL & "UNION ALL" & vbCrLf
    strSQL = strSQL & "SELECT * FROM T1 WHERE C1 BETWEEN 501 AND 520"
    snpData.Open strSQL, dbDatabase
    
    Do While Not snpData.EOF
        Debug.Print snpData("c1"), snpData("c2"), snpData("c3")
        snpData.MoveNext
    Loop
    
    snpData.Close
    dbDatabase.Close
End Sub

In the above code, change these lines to specify the user that owns the T1 table created earlier, the password for that user, and the database name (from tnsnames.ora) where the table is located:

    strDatabase = "MyDB"
    strUsername = "MyUser"
    strPassword = "MyPassword"

Show the Debug window (I think that the option is on the View menu) and run the above procedure by typing the following line in the Debug window, followed by an Enter key:

TestUnion

You should see something like this in the Debug window:

 1            3/7/2011      MONDAY   
 2            3/8/2011      TUESDAY  
 3            3/9/2011      WEDNESDAY
 4            3/10/2011     THURSDAY 
 5            3/11/2011     FRIDAY   
 6            3/12/2011     SATURDAY 
...
 414          4/23/2012     MONDAY   
 415          4/24/2012     TUESDAY  
 416          4/25/2012     WEDNESDAY
 417          4/26/2012     THURSDAY 
 418          4/27/2012     FRIDAY   
 419          4/28/2012     SATURDAY 
 420          4/29/2012     SUNDAY   
 421          4/30/2012     MONDAY   
 422          5/1/2012      TUESDAY  
 423          5/2/2012      WEDNESDAY
 424          5/3/2012      THURSDAY 
 425          5/4/2012      FRIDAY   
 426          5/5/2012      SATURDAY 
...
 454          6/2/2012      SATURDAY 
 455          6/3/2012      SUNDAY   
 456          6/4/2012      MONDAY   
 457          6/5/2012      TUESDAY  
 458          6/6/2012      WEDNESDAY
 459          6/7/2012      THURSDAY 
 460          6/8/2012      FRIDAY   
 461          6/9/2012      SATURDAY 
 462          6/10/2012     SUNDAY   
 463          6/11/2012     MONDAY   
 464          6/12/2012     TUESDAY  
 465          6/13/2012     WEDNESDAY
 466          6/14/2012     THURSDAY 
 467          6/15/2012     FRIDAY   
 468          6/16/2012     SATURDAY 
 469          6/17/2012     SUNDAY   
 470          6/18/2012     MONDAY   
 471          6/19/2012     TUESDAY  
 472          6/20/2012     WEDNESDAY
 473          6/21/2012     THURSDAY 
 474          6/22/2012     FRIDAY   
 475          6/23/2012     SATURDAY 
 476          6/24/2012     SUNDAY   
 477          6/25/2012     MONDAY   
...
 509          7/27/2012     FRIDAY   
 510          7/28/2012     SATURDAY 
 511          7/29/2012     SUNDAY   
 512          7/30/2012     MONDAY   
 513          7/31/2012     TUESDAY  
 514          8/1/2012      WEDNESDAY
 515          8/2/2012      THURSDAY 
 516          8/3/2012      FRIDAY   
 517          8/4/2012      SATURDAY 
 518          8/5/2012      SUNDAY   
 519          8/6/2012      MONDAY   
 520          8/7/2012      TUESDAY    

If the above works for you, try replacing the SQL statement in the TestUnion subroutine with the SQL statement that you are having trouble with – does the SQL statement return the expected results?

31 08 2012
Jan

Hej,

by executing the code as a makro in excel i get the error
“Could not connect to the database. Check your user name and password.
The Provider could not be found. maybe it isn’t installed.”

I changed the the ConnectionString to:

“Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=” & strDatabase & “)(PORT=” & strPort & “)))(CONNECT_DATA=(SID=” & strSid & “)(SERVER=DEDICATED)));User Id=” & strUserName & “;Password=” & strPassword & “;ChunkSize=1000;FetchSize=100;”

Because the Server is not my pc. But i get the same error, if i use your ConnectionString.

Can you help me please?

I use Excel 2003 on a Vista Box with userrights.

Greatings,

Jan

31 08 2012
Charles Hooper

Jan,

I am not sure how much help I will be able to provide to you. The error message that you received suggests one of three problems:
* If you are using the 32 bit version of Excel, you must have the 32 bit Oracle client installed (I believe that you will need the full client, not the Instant Client).
* When the Oracle client is installed, you will probably need to perform a custom installation and install all of the Windows interfaces (except the one with MTS in its name). In particular, you will need “Oracle Provider for OLE DB”.
* Finally, access rights on the computer need to be correct so that the user is able to access the Oracle client’s configuration files. If your user is not in the computer’s local administrator’s group, you may need to adjust the file permissions for the Oracle installation folder so that the user has at least read access to that folder and all of its contents. Additionally, User Access Control may need to be either set less restrictive or completely disabled.

In the script, where you see:

strDatabase = "MyDB"

MyDB is the name of the database from the computer’s tnsnames.ora file.

If the OraOLEDB.Oracle provider was not installed, you may be able to create an ODBC type connection instead, assuming that the ODBC drivers were installed. See this article for an example of connecting using ODBC instead of the OraOLEDB.Oracle provider:

http://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/

22 03 2013
psm1034

I have had to modify your code to use ODBC/DSN connection because Oracle OleDB is not available – I am connecting to an Oracle 10g database using the Instant Client. I get 34 rows of data back from the query. However when I run the same query in SQL Developer I get 138. I trust SQL Developer more than Excel VBA – so where should I look for the problem?

22 03 2013
psm1034

Please disregard. I used the following code to copy/paste the exact SQL query as formulated in my VBA and pasted/ran in SQL Developer. There was a problem in how I parsed variables and passed to SQL string.

Dim dClip As DataObject
Set dClip = New DataObject
dClip.SetText strSQL
dClip.PutInClipboard

I found the incorrect lines and corrected them. I’m good now.

22 03 2013
Charles Hooper

Great that you found the solution to the problem.

There are a couple of potential causes for the problem that you experienced, such as:
* VPD (Virtual Private Database) – assuming that you were connecting as diffrent database users
* Referencing objects in different schemas – again, easy to be affected if you were connecting as different database users
* Referencing objects in different databases – easy to be affected if one program is using an ODBC connection while another is using an OLEDB or similar type connection that directly references the tnsnames.ora

2 08 2013
rajasugan

Hi hooper thanks for your codding i used your code but i face some issue 1)Could not connect to the database. Check your user name and password. if i mentioned this code conn.CursorLocation = adUseClient sample from my code:

conn.CommandTimeout = 100000
conn.CursorLocation = adUseClient
conn.Open 

but i am not getting if i avoid the this line conn.CursorLocation = adUseClient
2)always the recordset.State=0 only i dont how to solve this issue
note:-

conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1526))" & _
"(CONNECT_DATA=(SID=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"

please help me

2 08 2013
rajasugan

this is my code corrcect me where i have to change thanks advance

Private Sub CommandButton1_Click()
Dim conn                                        ' As ADODB.Connection
Dim rs                                          'Dim  'As ADODB.Recordset
 
Dim xl
Dim wb
 
'Declare a set of variables to hold the username and password for the database
Dim strHost, strDatabase, pwd, strUser, strPassword As String
 
Dim i As Integer
Dim intResult As Integer
Dim strSQL As String
Dim lngRow As Long
    
On Error Resume Next
'Connect to the database
 
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.recordset")
 
'open the excel application
Set xl = CreateObject("Excel.Application")
  Set wb = xl.Workbooks.Open("C:\Users\Ah0166213\Desktop\sample_demo.xlsm")
  'Set ws = wb.Worksheets(1)
 
'variables to hold the username and password for the database
strHost = "server"
strDatabase = "dbname"
strUser = "user"
strPassword = "pwd"
 
'Oracle connection string
conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1526))" & _
"(CONNECT_DATA=(SID=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
MsgBox conn.ConnectionString
conn.CommandTimeout = 100000
'conn.CursorLocation = adUseClient
conn.Open
 
If (conn.State <> 1) Or (Err <> 0) Then
    intResult = MsgBox("Could not connect to the database.  Check your user name and password." & vbLf & Error(Err), 16, "Excel")
Else
    'strSQL =
    'Add a new worksheet to the new workbook, add after the last sheet
    ThisWorkbook.Sheets.Add After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet
    ThisWorkbook.ActiveSheet.Name = "Result"
    
    rs.Open "SELECT * FROM ps_job WHERE emplid='1234' order by effdt desc", conn
      
    MsgBox rs.State
    If rs.State = 1 Then
        'Slow Method------------------------------------
        lngRow = 0
        'Header Row
        For i = 0 To rs.Fields.Count–1
            lngRow = lngRow + 1
            ActiveSheet.Cells(lngRow, i + 1).Value = rs.Fields(i).Name
            ActiveSheet.Cells(lngRow, i + 1).Font.Bold = True
        Next i
   ' MsgBox "2"
        'Detail Rows
        Do While Not snpData.EOF
            lngRow = lngRow + 1
            For i = 0 To rs.Fields.Count - 1
                ActiveSheet.Cells(lngRow, i + 1).Value = rs.Fields(i)
            Next i
 
            snpData.MoveNext
        Loop
          'MsgBox "3"
        rs.Close
    End If
End If
 
     ' MsgBox "4"
conn.Close
Set rs = Nothing
Set conn = Nothing
End Sub
2 08 2013
Charles Hooper

I noticed that you commented out “As ADODB.Connection”. That indicates that you very likely need to add a reference to the Microsoft ActiveX Data Objects Library – the process for doing that is explained in this article:

http://hoopercharles.wordpress.com/2009/12/28/extract-1834-images-from-excel-2007-and-transfer-to-a-database-table/

Dim strHost, strDatabase, pwd, strUser, strPassword As String

In the above, the variables strHost, strDatabase, pwd, strUser are declared as variants, while only strPassword is declared as a string. I prefer not to declare multiple variables on a single line, but if you must do that, you should change the above as follows:

Dim strHost As String, strDatabase As String, pwd As String, strUser As String, strPassword As String

conn.ConnectionString = "Driver={Microsoft ODBC for Oracle}; " & _

For me, the Microsoft ODBC driver for Oracle Database stopped working when my company switched to Oracle 8i (8.1.7.3) back in 2001 or 2002. There may be other issues with the contents of your connection string, but I did not spend much time examining the connection string.

This is the connection string that my code sample uses:

dbVMFG.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUserName & ";Password=" & strPassword & ";ChunkSize

That “OraOLEDB.Oracle” provider is made available through a custom (or full) installation of the Oracle client when the “Oracle Provider for OLE DB” is selected. The Oracle Client installer version 11.2.0.3 misses a couple of key entries in the Windows registry – see this article for the missing entries:

http://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/

The “conn.CursorLocation = adUseClient” line is returning an error because the adUseClient constant does not have a defined value. The adUseClient constant is given the correct defined value when you add a reference to the Microsoft ActiveX Data Objects Library as mentioned earlier in my comment.

snpData.MoveNext

You will want to change the above to the following because you named the recordset rs rather than snpData:

rs.MoveNext

I might have missed a couple of issues in your code, but the above should be enough to help you get started.

5 08 2013
rajasugan

i feel happy for your reply but still i got some issue i leave reply in this

http://hoopercharles.wordpress.com/2012/11/25/connecting-to-an-oracle-database-with-visual-basic-6-0-on-windows-8-64-bit/#comment-5640

Thanks from heart becoz u spent time for me (the unknown person)

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 139 other followers

%d bloggers like this: