thanks everybody .... I have done everything that was suggested but running
into a snag .... it seems that I need to import *.xlsm files as well ... I
changed the following in the userforms code module:

FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")

to read

FName=Application.GetOpenFilename("Excel Files (*.xlsm),*.xlsm")

but I get the following error after selecting the file:

Run-time error '-2147467259 (80004005)':
External table is not in the expected format.

What am I doing wrong? Also, I want to be able to browse for *.xls files as
well.

Thanks for all your help so far.

Jeff

"Chip Pearson" wrote:

Here's one way. Create a new UserForm in the workbook into which the
sheet from another workbook will be imported. Add the following
controls to the form:

TextBox: name = tbxWorkbook
Button: name = btnBrowse caption = Browse
ListBox: name = lbxSheets
Button: name = btnCopySheet caption = "Copy Sheet"
Button: name = btnClose caption = "Close"

Then, you'll need to set a reference to the Microsoft ActiveX Data
Objects (ADO) library. In VBA, press CTRL G to open the Immediate
Window, enter the following all on a single line and then press ENTER:

ThisWorkbook.VBProject.References.AddFromGuid
"{B691E011-1797-432E-907A-4D8C69339129}",0,0

If this command throws an error, go to the Tools menu, choose
References, and scroll down to "Microsoft ActiveX Data Objects" and
check the entry with the highest version number. If you don't have ADO
installed, you're screwed.

Now, paste the following code into the userform's code module:

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' BEGIN CODE
'''''''''''''''''''''''''''''''''''''''''''''''''' ''
Private Sub btnBrowse_Click()
Dim FName As Variant
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName = False Then
Exit Sub
End If
Me.tbxWorkbook.Text = FName
ListSheets CStr(FName)
End Sub

Private Sub ListSheets(WBName As String)
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset
Dim TableName As String

Set CN = New ADODB.Connection
With CN
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & WBName & ";" & _
"Extended Properties=""Excel 8.0;"""
.Open
Set RS = .OpenSchema(adSchemaTables)
End With

Me.lbxSheets.Clear
Do While Not RS.EOF
TableName = RS.Fields("table_name").Value
If Right$(TableName, 1) = "$" Then
Me.lbxSheets.AddItem Left(TableName, Len(TableName) - 1)
End If
RS.MoveNext
Loop
RS.Close
CN.Close
End Sub

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub btnCopySheet_Click()
Dim WB As Workbook
Dim WS As Worksheet

If Me.lbxSheets.Value = vbNullString Then
Exit Sub
End If
Application.ScreenUpdating = False
Set WB = Application.Workbooks.Open(Me.tbxWorkbook.Text)
Set WS = WB.Worksheets(Me.lbxSheets.Value)
With ThisWorkbook.Worksheets
WS.Copy after:=.Item(.Count)
ActiveSheet.Name = "Import"
End With
WB.Close savechanges:=False
Application.ScreenUpdating = True
Unload Me
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''' ''
' END CODE
'''''''''''''''''''''''''''''''''''''''''''''''''' ''

Now, use

Sub ShowTheForm()
UserForm1.Show vbModal
End Sub

in a regular code module to display the form. When you click the
Browse button, you'll get a standard File Open dialog. Select the file
from which the worksheet is to be imported. The list box will display
all the sheet names in that workbook. Select the worksheet to be
imported in the list box. Click the Copy Sheet button. The selected
worksheet will be imported into the workbook and named "Import".

You can download a working example with all the code from
http://www.cpearson.com/Zips/ImportSheet.zip

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Thu, 8 Oct 2009 10:46:01 -0700, JeffJ
wrote:

I am trying to create a macro in file A that will ask the user to locate a
different file (name of file will change) and then ask which sheet from
stated file needs to be imported into file A and then name that "Imported".
Any help would greatly be appreciated.