Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete info from source file w/out changing the destination file | New Users to Excel | |||
Open Test file with VBA with a changing file number | Excel Programming | |||
Querying a CSV file from Excel | Excel Programming | |||
Importing/querying data from .mdb file into Excel | Excel Programming | |||
Open delimited text file to excel without changing data in that file | Excel Programming |