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)
