Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import data from a closed workbook | Excel Discussion (Misc queries) | |||
Macro to import from one closed workbook | Excel Programming | |||
import all sheets from a closed workbook | Excel Programming | |||
Import Sheet from Closed Workbook | Excel Programming | |||
Using Querytable.add to import data from a closed workbook | Excel Programming |