Import resultset from select query in .sql file to excel using VBA

[Skorben]

Hello Excel Experts,

First time poster here - long time lurker, but this is about to change.

I am having an issue with the following VBA code, which is supposed to do the following:
1) Connect to SQL Server
2) Use .sql file on network-drive to extract the desired select statement (sql-code)
3) Open recordset
4) Copy results to cells in excel-sheet

Sub sql_execute()

    'Declare variables'
        Set Cnn = New ADODB.Connection
        Set Rst = New ADODB.Recordset
        Dim SqlStatement As String
        Dim ConnectionString As String

ReportDateVariable = Left(Sheets("Sheet1").Cells(2, 2), 10) & " 00:00:00.000"

'Connection to SQl Server
ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=DB;" & _
"Data Source=Server"

Cnn.Open ConnectionString
Cnn.CommandTimeout = 900
 
SqlTextFile = "PathTo.sqlFile"
Debug.Print SqlTextFile
Dim hFile As Long
hFile = FreeFile
Open SqlTextFile For Input As #hFile
SqlStatement = Input$(LOF(hFile), hFile)
SqlStatement = Replace(SqlStatement, "ReportDateVariable", ReportDateVariable)
Close #hFile
Debug.Print SqlStatement
 
 
Rst.Open SqlStatement, Cnn
 

If Rst.State = adStateClosed Then
    MsgBox "Recordset is closed"
Else
    MsgBox "Recordset is open"
End If

'Copy the results to cell A6 in "Sheet1"
Set WSP1 = Sheets("Sheet1")
WSP1.Activate
If Rst.EOF = False Then WSP1.Cells(6, 1).CopyFromRecordset Rst

Rst.Close
Set Rst = Nothing

Cnn.Close
Set Cnn = Nothing

End Sub

Issue:
For some reason the recordset will not open. The msgbox always returns that the state of the recordset is closed. The error occurs at "If Rst.EOR = False Then".
The SQL-code is a regular select-statement. I have used this .sql-file before and succesfully achieved the above without any error (unfortunately I forgot to save the code), but for some reason after trying to replicate it I am getting this error.
Changing the variable "SqlStatement" to a "SELECT * FROM TABLE1" and the code works...

I am going out of my mind! You know the feeling...

I hope my description of the issue makes sense.
If not, please do not hesitate to ask.

Thanks in advance,

Skorben

 

[Skorben]

Solved it!
For some reason the string in the variable was not acting correctly in terms of spacing.
Fixed some of the SQL-code and it worked.

If anyone at some point runs into the same problem, please do not hesitate to PM me.