Re: SQLSetConnectAttr error with Excel - converting to an older database?

I would like to take this concept further. I hope it is possible.

Forgive me for crossposting, but this is related: I need to pull data from a daily generated excel file, with a changing
file name. The file will always be in the same location.

I ran the connection info on the excel file I would like to hit and it came back with this:

ODBC;DSN=Excel Files;DBQ=C:\ExcelFiles\ExcelFile - Mar. 2, 07.xls;DefaultDir=C:\ExcelFiles
\;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

What I want to do is add a query with a piece of VBA so that it will always pull in the ExcelFile of the current date [ie "Excelfile -
" & format(date, "Mmm. dd, yy") & ".xls" ]

Can anyone help with that?

***

And further :
***

The excel file reference needs to be in 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 Received on Fri Mar 02 2007 - 15:44:06 CST