Import All Sheets from Closed Workbook

[Guest]

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--

 

[Bob Phillips]

Why not just open it, copy the sheets over, and then close it?

--
HTH

Bob

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

 

[Ron de Bruin]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Ron de Bruin]

If you use MultiSelect then you must loop through the array

Try somthing like this

This is the path that it open by default now, you can browse to another folder.
MyPath = "C:\Data"


Sub Test()
Dim basebook As Workbook
Dim mybook As Workbook
Dim N As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath

FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ActiveWorkbook

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
mybook.Worksheets.Copy after:= _
basebook.Sheets(basebook.Sheets.Count)

mybook.Close False
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

 

[Guest]

UNREAL!!! This is exactly what I wanted. I didn’t know it required a
For…Next loop. How did you know that a For…Next loop was required to achieve
the results? The 2005 post did not use this technique.

 

[Guest]

Yep, that did it!! Ron, both Bob and yourself used a For...Next loop. Why
did you use the For...Next loop this time? The 2005 post did not use this
technique.

 

[Ron de Bruin]

Hi ryguy7272

The 2005 post not use GetOpenFilename with multiselect but a fixed file.
No need to loop then




--

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

Yep, that did it!! Ron, both Bob and yourself used a For...Next loop. Why
did you use the For...Next loop this time? The 2005 post did not use this
technique.

 

[Guest]

Oh! So simple and yet so powerful.

--
RyGuy

Hi ryguy7272

The 2005 post not use GetOpenFilename with multiselect but a fixed file.
No need to loop then

 

[Bob Phillips]

Because it was using MultiSelect=True, and it referenced an item in an
array. Conclusion, it was missing the loop control code.

--
HTH

Bob

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

UNREAL!!! This is exactly what I wanted. I didn't know it required a
For.Next loop. How did you know that a For.Next loop was required to
achieve
the results? The 2005 post did not use this technique.