Querying an Excel File with a changing file name

[S Davis]

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 (tip --

see this useful thread here:

http://groups.google.ca/group/comp....browse_thread/thread/20200ae298c993a4/?hl=en#
or tips on gathering the connection information and SQL from a broken
query) 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 refresh 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?

 

[S Davis]

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