Xml data import

[petep]

Hi I need some help, I recorded a Macro which imports an excel file using the XML Data import function in the data ribbon. the code is below, but the issue i am having is on the name of the file. I have bolded the area that changes daily. the file name changes everyday. is there anyway to make that part that changes a wildcard so i dont have to worry about the daily name change?

Sub ()
'
Application.ScreenUpdating = False


Clear




    DF = Format(Sheets("Last Night COB").Range("D1"), "yyyymmdd")
    DF1 = Format(Sheets("Last Night COB").Range("D1"), "mmddyyyy")
    DF2 = Format(Sheets("Last Night COB").Range("F1"), "yyyymmdd")
    DF3 = Format(Sheets("Last Night COB").Range("F2"), "yyyymmdd")
    DF4 = Format(Sheets("Last Night COB").Range("F1"), "mmddyyyy")
'
    Sheets("Last Night COB").Select
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
        "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C" & DF2 & "\MANAGEMENT LP - VAR" _
        , _
        " SWAP - DAILY[B]-01-Mar-2016-109333523[/B].XLS;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OL" _
        , _
        "EDB: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 System Database=Fals" _
        , _
        "e;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;J" _
        , _
        "et OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False" _
        ), Destination:=Range("$A$8")).QueryTable
        .CommandType = xlCmdTable
        .CommandText = Array("'Equity-T1$'")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceDataFile = _
        "I:\RawhideData\Email\" & DF2 & "\MANAGEMENT LP - VAR SWAP - DAILY[B]-01-Mar-2016-109333523[/B].XLS"
        .ListObject.DisplayName = _
        "Table_MANAGEMENT_LP___VAR_SWAP___DAILY[B]_01_Mar_2016_109333523[/B]"
        .Refresh BackgroundQuery:=False
    End With
    
    Sheets("Last Night COB").Select
    Range("A11").Select
    ActiveSheet.ListObjects( _
        "Table_MANAGEMENT_LP___VAR_SWAP___DAILY[B]_01_Mar_2016_109333523[/B]").Unlist
    Rows("7:8").Select
    Selection.Delete Shift:=xlUp

this seems to work but i cant figure out the other parts.

        "I:\RawhideData\Email\" & DF2 & "\MANAGEMENT LP - VAR SWAP - DAILY[B]*[/B].XLS"

thanks everyone!

 

[petep]

just bumping to see if anyone out there may be able to help