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.

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.

There are two ways to do this - one involved opening the other
workbook using the current instance, and the other involves creating a
separate Excel application instance and opening it in that.

The first one looks like this:

Application.Dialogs(xlDialogOpen).Show

That displays the File Open dialog and opens the selected spreadsheet,
which will then be the current ActiveWorkbook, so you need to save a
reference to the original workbook in an object. Not sure what happens
if you press Escape or how you handle that.

The other way looks like this:

Dim oExcel As Excel.Application 'The Excel Application
Object used to control excel
Set oExcel = New Excel.Application
importFile = Application.GetOpenFilename("Excel Workbooks (*.xls),
*.xls")
Set oWorkbook = oExcel.Workbooks.Open(importFile, 0, True, , , ,
True)

Then, to copy the data across, you could do this:

oWorkbook.Sheets("Sheet1").Activate
Cells.Select
Selection.Copy
oMainWorkbook.Activate
ActiveSheet.Paste

I did originally do it with the proper Worksheet copy API but that
broke when I had cells that had more than 255 characters so I did the
copy/paste route.

Remember to close things down afterwards. Let me know if you get
stuck.

Phil Hibbs.

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.

I just noticed that you need to prompt for the sheet name - not sure
how you would do that, but you'd have to open the workbook first and
then present a list of sheet names. This might need a User Form.

Phil Hibbs.

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.