VBA Oracel connection OLEDB Not returning data

I am using the below code in excel to connect to oracle and read in a text file with the sql script in it that returns data to multiple tabs.  The problem I am having is that it is not coping the data to excel.   I can see the queries running on the oracle side and completing.  I also tested the query to make sure it returned data and it works.  I have used the script and connected to sql server and had no problems.  I am not sure if  .copyfromrecordset  does not work with an oledb oracle connection.  Please help.

Sub test()
 ExecuteSqlScript ("C:\test.sql")
End Sub
Sub ExecuteSqlScript(FilePath As String)
 
    Dim Script As String
    Dim FileNumber As Integer
    Dim Delimiter As String
    Dim aSubscript() As String
    Dim Subscript As String
    Dim i As Long
    Dim cn As New ADODB.Connection
    Dim comm As New ADODB.Command
    Dim rs As New ADODB.Recordset
    Dim dbConnectStr As String
    Dim strSheet As String
    Dim strRange As String
    Dim objCmd As New ADODB.Command 

    
     UID = "UID"
    Password = "Pword"
    Srv = "srv"

    'dbConnectStr
    cn.ConnectionString = "Provider=OraOLEDB.Oracle;" & _
                          "DATA Source=" & Srv & ";" & _
                          "User Id=" & UID & ";" & _
                          "Password=" & Password
    cn.ConnectionTimeout = 0
      
    
    On Error Resume Next
    cn.Open
    If Err.Number <> 0 Then
        MsgBox "Error "
        Exit Sub
    End If
    On Error GoTo 0
    

    Set comm.ActiveConnection = cn
    rs.ActiveConnection = cn


 
    Delimiter = ";"
    FileNumber = FreeFile
    Script = String(FileLen(FilePath), vbNullChar)
 
    ' Grab the scripts inside the file
    Open FilePath For Binary As #FileNumber
    Get #FileNumber, , Script
    Close #FileNumber
 
    ' Put the scripts into an array
    aSubscript = Split(Script, Delimiter)
 
    ' Run each script in the array
    For i = 0 To UBound(aSubscript) - 1
        aSubscript(i) = Trim(aSubscript(i))
        Subscript = aSubscript(i)
'        Debug.Print Subscript
        comm.CommandText = Subscript
        comm.CommandTimeout = 0
        rs.Open comm
        
        If i = 0 Then Sheets("Sheet1").Range("A2").CopyFromRecordset rs
        If i = 1 Then Sheets("2").Range("A2").CopyFromRecordset rs
        If i = 2 Then Sheets("3").Range("A2").CopyFromRecordset rs
    rs.Close
  
    Next i
MsgBox "done"
End Sub

Open in new window

LVL 1
montrofAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
try to do:
rs.MoveLast
rs.MoveFirst

Open in new window

before each CopyFromRecordset call. The idea is to populate the rs first so that CopyFromRecordset knows that there are records in the recordset
0
montrofAuthor Commented:
when I try this it gives me a message saying saying rowset does not support fetching backwards.

Thanks
Montrof
0
montrofAuthor Commented:
I finally figured out the problem.  I was passing the date in the query like '1-jan-2015' and when vba passes it to Oracle it does not like that format so you have to do it as TO_DATE('01/01/2015','DD/MM/YYYY') .  

Montrof
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
montrofAuthor Commented:
No answer was provided and I was able to solve the problem on my own.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.