Having issues passing a Variable to RS.Open

amit.narayan.rai

New Member
Joined
Apr 3, 2012
Messages
10
Hi,

I am trying to pull data from Oracle Database to Excel using OLEDB.

Here is the code...which is giving me problems..

Code:
Dim SqlScriptName As String
    Dim SqlScript As String
    Dim FilePath As String
Sub SQLScripts()
SqlScriptName = frmInput.lstSQLScripts.Value


    Dim RS As ADODB.Recordset
    Dim col As Integer
    Dim row As Integer
        SqlScriptName = frmInput.lstSQLScripts.Value
        Sheets(SqlScriptName).Visible = True
        Sheets(SqlScriptName).Select
        Call CleanUniversal


        Set cN = New ADODB.Connection
        Set RS = New ADODB.Recordset
  
        SqlScript = GetFileContent(FilePath)
    
cN.Open strConnect
                        
RS.Open SqlScript, cN
   
' Now actual data as fetched from select statement
  col = 0
  row = 1
  Do While Not RS.EOF
    row = row + 1
    col = 0
    
    Do While col < RS.Fields.Count
      Cells(row, col + 1) = RS.Fields(col).Value
      col = col + 1
    Loop
  
    RS.MoveNext
  Loop
End Sub

GetFileContent is a small function which parses data from a text file and puts into SqlScript variable.

The issue is when I try to pass the actual script to RS.Open it works fine...like

RS.Open "Select * from TABLE_NAME, cN

but when i use the function GetFileContent to read the same query lying outside...it does not work..however it does not give me an error too..

Here is the code for GetFileContent..

Code:
Function GetFileContent(Name As String) As String
    Dim intUnit As Integer
    
    On Error GoTo ErrGetFileContent
    intUnit = FreeFile
    Open Name For Input As intUnit
    GetFileContent = Input(LOF(intUnit), intUnit)
ErrGetFileContent:
    Close intUnit
    Exit Function
End Function

I have tried to Debug the Code and SqlScript variable correctly imports the text from text file.

Any help will be very much appreciated..

Thanks
Amit Narayan
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I would do this:

Code:
Debug.Print SqlScript

and make sure that what's in the Immediate Window is exactly what you expect.
 

amit.narayan.rai

New Member
Joined
Apr 3, 2012
Messages
10
Thanks Andrew...I have tried that and it seems that when I am fetching the Text from Text file using GetFileContent function the variable which I need to replace is coming in String format..

example....
function is fetching query like this

select * from TABLE_NAME where COLUMN = '" & VARIABLE "'

VARIBALE is a vb variable which gets assigned from a USERFORM like this : frmInput.cboStudy2.Value

when I passing direct query instead of SqlScript..it is working fine..

Thanks
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
You can't do that I'm afraid, you will need to replace the bits in your string with variables in code, for example, you can wrap variables in curly brackets:
Code:
SqlScript = "select * from TABLE_NAME where COLUMN = '{VARIABLE1}'"
Then you can use
Code:
sqlscript = replace(slqscript,"{VARIABLE1}", MyVBAVariable)
 

amit.narayan.rai

New Member
Joined
Apr 3, 2012
Messages
10
Thanks for your reply Kyle....Fortunately..I was able to do exactly this...and just wanted to thank all you guys for awesome support...

Cheers..
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top