Running SQL file from VBA

Question

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

cboden · Accepted Answer · 2014-11-20 16:53:44Z

3

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

edited Nov 20, 2014 at 16:53

answered Nov 20, 2014 at 16:26

cboden

8239 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

titan31 Over a year ago

Would it be possible to add two variables to vba for dates that would be passed into the sql and used. Ideally, user would just enter two dates into two cells, run the macro and once the code is done running, it'd populate into excel. Or would that be a bit too much outside what VBA and Excel can do? If anyone can recommend any good books or guides into learning VBA, I'd appreciate it too. Only getting started on doing anything like this

cboden Over a year ago

this shouldn't be a problem. You can put something like ##date## into your SQL file. After importing that file you can use the replace() function to swap it with the cell values.

Collectives™ on Stack Overflow

Running SQL file from VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related