Import All Sheets from Closed Workbook
 

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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

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)

"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

Import All Sheets from Closed Workbook
 

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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

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 (its
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 doesnt 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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

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

Import All Sheets from Closed Workbook
 

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



--

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


"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 (its
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 doesnt 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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

UNREAL!!! This is exactly what I wanted. I didnt 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.
--
RyGuy


"Bob Phillips" wrote:

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

Import All Sheets from Closed Workbook
 

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


"Ron de Bruin" wrote:

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



--

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


"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 (its
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 doesnt 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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

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


"ryguy7272" wrote in message ...
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.
--
RyGuy


"Ron de Bruin" wrote:

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



--

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


"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 (its
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 doesnt 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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

Oh! So simple and yet so powerful.

--
RyGuy


"Ron de Bruin" wrote:

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


"ryguy7272" wrote in message ...
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.
--
RyGuy


"Ron de Bruin" wrote:

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



--

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


"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 (its
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 doesnt 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 dont know how to do it. I viewed Ron de Bruins 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

Import All Sheets from Closed Workbook
 

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)

"ryguy7272" wrote in message
...
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.
--
RyGuy


"Bob Phillips" wrote:

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