How do I prompt the user to select the file, when using macro to import a data file into a new tab?

Question

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

Solution

You can use GetOpenFilename:

.SourceDataFile = Application.GetOpenFilename("Excel workbooks (*.xls), *.xls")

Another option is the FileDialog object. It offers more flexibility.

Dim fdgOpen As FileDialog
Set fdgOpen = Application.FileDialog(msoFileDialogOpen)
fdgOpen.Title = "Please open a data file..."
fdgOpen.InitialFileName = "C:\MyDocuments\MyDir\"
'Other settings...
fdgOpen.Show
.SourceDataFile = fdgOpen.SelectedItems(1)

Comments

[Jon]

How does the FileDialog object fit in with my current code? When doing the file import manually, I need to select the tab from the excel file that will be imported. in this case, "_All_Demand$". What additional code is needed --- What do i need to include for Excel to take action on the Data source selected?

[Jean-François Corbett]

Well, in your code you write .SourceDataFile = "\\Folder\ImportFile_2011.04.05.xls". I assume this is where you want to prompt the user, right? Just replace that line with either of my examples above. And read VBA help on GetOpenFilename and/or FileDialog.

[Jean-François Corbett]

As for selecting the tab, that's a whole other matter. Why don't you ask a new question to avoid crowding this one. FYI, remember to accept this answer if you find it useful.