How do I prompt the user to select the file, when using macro to import a data file into a new tab?
I have a macro that is currently creates a new sheet, and imports another excel file into this new sheet.  

Data from this sheet is than pulled into other areas of the workbook.  

The file that is being imported will constantly have a different file name.  How do I adjust the below code to prompt the user to select the file?  (The directory will not change).


Sub ImportDemand()
Sheets.Add

    Sheets(2).Select
    Sheets(2).Name = "ImportedDemand"
    Range("E42").Select
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=\\Folder\ImportFile_2011.04.05.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("_All_Demand$")
        .Name = "ImportFile_2011.04.05"
        .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 = _
        "\\Folder\ImportFile_2011.04.05.xls"
        .Refresh BackgroundQuery:=False
    End With
End Sub
How do I prompt the user to select the file, when using macro to import a data file into a new tab?
===

I have a macro that is currently creates a new sheet, and imports another excel file into this new sheet.  

Data from this sheet is than pulled into other areas of the workbook.  

The file that is being imported will constantly have a different file name.  How do I adjust the below code to prompt the user to select the file?  (The directory will not change).


Sub ImportDemand()
Sheets.Add

    Sheets(2).Select
    Sheets(2).Name = "ImportedDemand"
    Range("E42").Select
    With ActiveSheet.QueryTables.Add(Connection:=Array( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=\\Folder\ImportFile_2011.04.05.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("_All_Demand$")
        .Name = "ImportFile_2011.04.05"
        .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 = _
        "\\Folder\ImportFile_2011.04.05.xls"
        .Refresh BackgroundQuery:=False
    End With
End Sub