CODEWITHSUNDEEP
sqlvba
titan31
titan31

Reputation: 195

Running SQL file from VBA

I'm currently trying to figure out how to run a .sql file from VBA. I can get the VBA to run sql code inside it, but as I have the SQL script already wrote, I'd like to just be able to call the file, run the code and pass the data back to Excel. Even if it's calling the file and passing the code back to VBA to run, that'd be fine, but it's just easier to maintain the code there instead of in the VBA editor.

Database is Oracle, but I've got the connection running and working atm.

' Makes the SQL call to collect the data.
Dim strConOracle As String
Dim oConOracle, oRsOracle
Dim strSQL As String
Dim lngCount As Long
Dim strCount As String



strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
oConOracle.Open strConOracle

strSQL = ""

Set oRsOracle = oConOracle.Execute(strSQL)

ActiveSheet.Cells.CopyFromRecordset oRsOracle

oConOracle.Close
Set oRsOracle = Nothing
Set oConOracle = Nothing

That's the code that I have atm, minus the database connection which is declared at the top.

Ideally, I'd just be changing the strSQL part.

Thanks for any help

Upvotes: 2

Views: 10279

Answers (1)

cboden
cboden

Reputation: 823

something like that will do the job for you:

Dim strSQL As String
strSQL = ""

Dim hnd As Integer
hnd = FreeFile

Open "C:\Temp\yourScript.sql" For Input As hnd

Dim row As String
Do Until EOF(hnd)
    Line Input #hnd, row
    strSQL = strSQL & row & vbNewLine
Loop

Close #hnd

Upvotes: 3

Related Questions