How do I prompt the user to select the file and sheet, when using macro to import a data file?

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 then pulled into other areas of the workbook.

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

I tried using the FileDialog object, but it doesn't seem that Excel takes any action on the file selected. And, this does not allow you to choose the tab/sheet to import.

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 database="""";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 System Database=False;Jet OLEDB:Encrypt Database=False; _
Jet OLEDB:Don't Copy Locale on Compact=False; _
Jet OLEDB:Compact Without Replica Repair=False; _
Jet OLEDB:SFP=False"), Destination:=Range("A1"))

.CommandType = xlCmdTable
.CommandText = Array("_All_Demand$")
.Name = "ImportFile_2011.04.05"
'Other Settings
.SourceDataFile = _
"\\Folder\ImportFile_2011.04.05.xls"
.Refresh BackgroundQuery:=False
End With

Answer 1

From Excel 2002 VBA: Programmer's Reference:

dim fd as filedialog
dim ffs as filedialogfilters
dim stFileName as string
dim wkb as workbook

set fd=application.filedialog(msofiledialogopen)
with fd
  set ffs=.filters
  with ffs
    .clear
    .add "Excel", "*.xls" 'Or whatever version you are using.
  end with
  .allowmultiselect=false
  if .show=false then exit sub
  set wkb=getobject(.selecteditems(1))
end with

Here you can just use an input box to have the user write the name of the tab or you can launch the filedialog box from a userform (the better option).

Assuming you use a userform you can load the tab names into a drop down box and have the user select which tab it will be. Then do your code.

Another alternative is loading the tabs into your main workbook and have the user select it from there.

Answer 2

Here's a way to return the name of a user-selected sheet:

    varCellContent = Application.InputBox _
        (prompt:="Choose a sheet by clicking on any cell in it.", Type:=8)

    strDestinationSheetName = ActiveSheet.Name

How to return the path of a user-selected file was already explained in answers to your previous question. With the FileDialog object, or if you don't like it, with GetOpenFilename:

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

Now FileDialog or GetOpenFilename will return the path of the file to open, e.g. "\\Folder\ImportFile_2011.04.05.xls", but they will not actually open the file. You have to use the returned path in the appropriate manner. From your question it isn't clear what this is, but I would guess:

.SourceDataFile = strPathOfFileToOpen 

and/or

Data Source=strPathOfFileToOpen ; _

I'm not quite sure why you don't have quotes around the latter in your question.

Same logic with the sheet name: I've pointed out how to return it, but without more details I can't say how you should use it.