Hello All,
I have a need to import a text file that stores a SQL query that is over 1K lines of code directly into the Command Text of a ODBC data connection in Excel. I know the query works if I manually copy/paste the code directly into the command text and refresh the connection.
Here is the code I am working with. I am using a reference in VBA to the Microsoft Scripting Runtime library:
I know this code works with a simple query like SELECT * FROM TABLE but when I attempt to use the more complicated query I get a run time error 1004 Application Defined or Object Defined Error.
I imagine this is some sort of limitation revolving around the number of lines in the code.
Does anyone have a better method or perhaps knows a workaround to the issue I am facing?
I have a need to import a text file that stores a SQL query that is over 1K lines of code directly into the Command Text of a ODBC data connection in Excel. I know the query works if I manually copy/paste the code directly into the command text and refresh the connection.
Here is the code I am working with. I am using a reference in VBA to the Microsoft Scripting Runtime library:
Code:
Dim ts As TextStream
Dim fso
Dim s As String
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("scripting.filesystemobject")
Set ts = fso.OpenTextFile("c:\test\SQL.txt", ForReading, False)
s = ts.ReadAll()
With ActiveWorkbook.Connections("SQL Query").ODBCConnection
.BackgroundQuery = False
.CommandText = s
End With
End Sub
I know this code works with a simple query like SELECT * FROM TABLE but when I attempt to use the more complicated query I get a run time error 1004 Application Defined or Object Defined Error.
I imagine this is some sort of limitation revolving around the number of lines in the code.
Does anyone have a better method or perhaps knows a workaround to the issue I am facing?