Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim x As Long
Dim FilesToOpen


FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Excel Files to Open")

Set Wb1 = ActiveWorkbook
For x = LBound(FilesToOpen) To UBound(FilesToOpen)
Set Wb2 = Workbooks.Open(Filename:=FilesToOpen(x))

Wb2.Worksheets.Copy _
after:=Wb1.Sheets(Wb1.Sheets.Count)

Wb2.Close False
Next x

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ryguy7272" wrote in message
...
Somehow I knew you would find me Ron! It is a pleasure and privilege to
learn from someone like you. I found some of your old code, from back in
2005 I believe, that you posted to the DG. It does almost what I want (it's
always almost, right). What I am trying to do is let the user select the
file that the data is imported from. I thought the 'FilesToOpen' line of
code would let the user choose which file to import from and then this
would
become the variable that is assigned to Wb2. However, it doesn't seem to
be
working, and it just crashes at line Wb2... Arrrrgggghhh..........Please
help.

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim FilesToOpen


FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.xls), *.xls", _
MultiSelect:=True, Title:="Excel Files to Open")

Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open(Filename:=FilesToOpen(x))

Wb2.Worksheets.Copy _
after:=Wb1.Sheets(Wb1.Sheets.Count)

Wb2.Close False

End Sub



Original code from the 2005 DG post:
Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Worksheets.copy _
after:=Wb1.Sheets(WB1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub

--
RyGuy


"Ron de Bruin" wrote:

No you must open the workbooks
See this page
http://www.rondebruin.nl/fso.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"ryguy7272" wrote in message
...
Is there a way to copy, or import, all sheets from a closed workbook
(workbook#1), into an open workbook? I am certain that this can be
done.I
just don't know how to do it. I viewed Ron de Bruin's site, and was
specifically interested in code at the following URL:
http://www.rondebruin.nl/ado.htm#choose

It is pretty awesome!! I am thinking there must be a way to import not
only
a sheet that a user chooses, but an entire workbook, with all sheets.

Can this be done? If so, how so.

TIA,
Ryan--



--
RyGuy