VBA to use .txt file as SQL query

[HRIS]

I have been trying to figure out a way in Excel VBA to run a very large SQL query. I have code that has worked for smaller queries in the past but I am struggling to get it to work with a large query as I can't just enter the query text into the macro (too big and extremely difficult to troubleshoot where I entered something incorrectly).

My query works fine in Microsoft SQL Server. I am hoping that I can copy the query into a .txt file and have Excel VBA perform the SQL query found in the .txt file. Is this possible to do with Excel VBA and does anyone know how I'd greatly appreciate any help I can get!!

Below is the code I have used to run simple SQL queries - with the last one being the one I actually run and it calls the other functions:

Option Explicit
Private CN As ADODB.Connection
Function Connect(Server As String, _
Database As String) As Boolean

Set CN = New ADODB.Connection
On Error Resume Next
With CN
    ' Create connecting string
    .ConnectionString = "Provider=SQLOLEDB.1;" & _
        "Integrated Security=SSPI;" & _
        "Server=" & Server & ";" & _
        "Database=" & Database & ";"
    ' Open connection
    .Open
End With

' Check connection state
If CN.State = 0 Then
    Connect = False
Else
    Connect = True
End If
End Function

Function Query(SQL As String)
Dim RS As ADODB.Recordset
Dim Field As ADODB.Field
Dim Col As Long
Dim a

' Open up a recordset / run query
Set RS = New ADODB.Recordset
RS.Open SQL, CN, adOpenStatic, adLockReadOnly, adCmdText

If RS.State Then
    Col = 1
    ' Output the column headings in the first row
    For Each Field In RS.Fields
    Cells(2, Col) = Field.Name
    Col = Col + 1
    Next Field
    
    ' Output the results in the rest of the worksheet
    a = Application.WorksheetFunction.CountA(Range("A:A")) + 2
    
    Cells(a, 1).CopyFromRecordset RS
    Set RS = Nothing
End If
End Function

Function Disconnect()
' Close connection
CN.Close
End Function

Sub NonEEQuery()

Dim SQL As String
Dim Connected As Boolean
Dim RangeSearch As Range
Dim RangeFound As Range
Dim LookupValue As String

Application.ScreenUpdating = False
Application.EnableEvents = False
 
' Our query
SQL = "Select * From MyTable Where MyColumn = MyCriteria"

' Connect to the database
Connected = Connect("MyServer", "MyDatabase")

If Connected Then
    ' If connected run query and disconnect
    Call Query(SQL)
    Call Disconnect
Else
    ' Couldn't connect
    MsgBox "Could Not Connect!"
    GoTo Finish
End If

Finish:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

 

[jessnault]

I was curious about this as well. I know this is an old post, but 2ish years later I have the same question. I haven't noticed any answers to similar questions. So I think I have it figured out. jic someone like me happens to be looking for this kind of solution....

edit your NonEEQuery() code to:

LQS = myQry("c:\temp\test2.txt")
SQLin = myQry("c:\temp\IN.txt")
SQL = LQS & " " & SQLin

add this function:

Function myQry(file As String)
Dim objFSO As Object
Dim objFile As Object
Dim strLine As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(file, 1)
Do Until objFile.AtEndOfStream
 strLine = strLine & vbNewLine & objFile.ReadLine
Loop
objFile.Close
'msgbox strLine
myQry = strLine
End Function

Why read two files? So you can run a query with an IN statement. The second file will hold your values clause and ordering, groupby or whatever comes at the end.

example:
test2 contains: Select Products, Price, QuantityOnHand From MyTable Where MyColumn = MyCriteria

IN contains: WHERE Products IN ('Accessories','Clothing','Bikes')

well, what if I don't have any IN statement? what then?
Then your query won't need an "WHERE x IN" statement and your IN statement file can be empty.

Or, you can create a form that loads a file list, from a folder you save your queries, into a listbox.
Using an if statement, If you choose a file with "_IN_" in the name, or whatever, then it can run the function Twice (once on the chosen file, a second time on a set IN file), appending the IN file contents to the first string results.

Now you have a little form that lets you choose which query to run, and IF it has an _IN_ in its name it will append the IN statement file. Which hopefully you updated before running. Now your code is versatile, rather than static and stuck to one query. Add this to your VBA ribbon of custom made VBA tools and you can run any query from a folder of queries to any sheet someone needs results on, at the click of a button.

You can uncomment the msgbox to test and call from a sub. I tried it on SQL server 2014, adventureworks, query of 42 lines. Returned 60,000+ rows. For the query with an IN statement, I used the 40ish line query and an in statement with 200 values each on a separate line. Everything worked great.

Will this work for oracle and their oracle instant connect client, or whatever it's called? Not sure. We're loading a file to a string, and then passing it. Last time I did this w/ Oracle the results did not return. Said there was an error w/ my query, which was copied from a query verified to work. Which leads me to believe Oracle's connection client maybe can't read in a long query via a string. I may try it w/o the carriage returns.

 

[mole999]

Go Here > Instant SQL Formatter

set output to VB

paste you code

run you code

varname1 = ""
varname1 = varname1 & "test2"
varname1 = varname1 & "contains:"
varname1 = varname1 & "Select Products, Price, QuantityOnHand From MyTable Where MyColumn = MyCriteria " & vbCrLf
varname1 = varname1 & "IN"
varname1 = varname1 & "contains: WHERE Products IN ('Accessories','Clothing','Bikes') " & vbCrLf
varname1 = varname1 & "well, what"
varname1 = varname1 & "if I don't have any IN statement? what then? " & vbCrLf
varname1 = varname1 & "Then your query won't need an ""WHERE x IN"" statement and your IN statement file can be empty."

you could delete & vbCrLf if you wanted too, and you can write and update the code live in excel


varname1 = ""
varname1 = varname1 & "Select * " & vbCrLf
varname1 = varname1 & "From dbo.something " & vbCrLf
varname1 = varname1 & "Where " & vbCrLf
varname1 = varname1 & "Order by 1"

 

[jessnault]

Interesting. So instead we could use the instant sql format page to copy and paste sql code into, copy the results, and paste it into our vba code. Then whenever we want to change queries we could just sift through our code and change the query. I can use that test my theory on oracle's connection client.

You can also reference a cell. Ex. SQL=range("A2").value
where A2 could equal CONCATENATE("SELECT ", A3," FROM"), CONCATENATE(" Adventureworks Where XYZ >=", A3, ";")

I've made a form with a drop down list that takes a title in A1 but uses the concatenation in A2 of the adjacent cells which hold the query. The query is easily edited in excel. But using excel and formulas is a bit of a pain. I'd rather just save my current queries from sql server management studio to a new folder location. Then have the vba read them directly. If I have to edit a query I could always open notepad, change the query, even save it as a different file name. No coding, no excel formulas, just text in notepad.