Querying an Excel File with a changing file name
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 he http://groups.google.ca/group/comp.d...c993a4/?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? |
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 |
All times are GMT +1. The time now is 02:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com