Importing Excel file saved as current date

[NeedSomeSeriousHelp77]

Hi All,

Hopefully a simple question. I'd like to import data into an excel file, from another excel file. The "other" excel file will always be saved with the current date i.e. 20110715.xls. I require the code to pick the file with the current date whenever it's run, i.e. if its run today, it'll pull from file 20110715.xls, if ran tomorrow, it'll pull from file 20110716.xls etc.

Here's the current code (in red im assuming are area's of the code that needs to change)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\20110715.xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System d" _
, _
"atabase="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;" _
, _
"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create Sys" _
, _
"tem Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Repli" _
, "ca Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("MASTER$")
.Name = "20110715"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"C:\Documents and Settings\20110715.xls"
.Refresh BackgroundQuery:=False
End With
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=8, Criteria1:="="
Selection.AutoFilter Field:=8, Criteria1:="<>"

Thank you!

 

[VoG]

Try like this

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Documents and Settings\" & Format(Date, "yyyymmdd") & ".xls;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System d" _
, _
"atabase="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;" _
, _
"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create Sys" _
, _
"tem Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Repli" _
, "ca Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("A1"))

 

[NeedSomeSeriousHelp77]

Thank you so much for your help VoG. I was so close, but you were able to correct me where I was going wrong. Thank you again!