Querying an Excel File with a changing file name
This will help for those who do not read the link I provided.
I need to somehow get a dynamic Excel file name into the following
code:
Sub AddQT()
Dim qt As QueryTable
MsgBox ActiveSheet.QueryTables(1).CommandText
MsgBox ActiveSheet.QueryTables(1).Connection
sqlstring = ActiveSheet.QueryTables(1).CommandText
connstring = _
"ODBC;DSN=nameofdsn;UID=userid;PWD=password;SERVER =dsnid.server.com;"
With ActiveSheet.QueryTables.Add(connstring, _
Destination:=Range("I1"), Sql:=sqlstring)
.Refresh
End With
End Sub
.... with the connection info I mentioned in the previous post.
As you should quickly see, trying to have a dynamic excel file name
requires the use of quotations, which effectively kills the
connstring.
Thanks
|