Copying Sheets from a closed workbook into an open workbook 
Author Message
 Copying Sheets from a closed workbook into an open workbook

Hi all,
I  want to import all sheets from a closed workbook into my open workbook
and then delete all the existing sheets that were there before the import.
Here's what I have so far:

Sub ImportData
    Dim ImportWorkbook As Workbook
    'Rename 1st Sheet to Dummy
    ActiveWorkbook.Worksheets(1).Name = "Dummy"
    'Use common dialog control to find required excel workbook for import
    Dim OpenFileName As String
    frmProgressBar1.DlgImport.Flags = 0
    frmProgressBar1.DlgImport.CancelError = True
    frmProgressBar1.DlgImport.Filter = "Import Workbooks  " &
"(*.xls)|*.xls|All files " & "(*.*)|*.*"
    frmProgressBar1.DlgImport.ShowOpen
    OpenFileName = frmProgressBar1.DlgImport.filename

I renamed the 1st sheet to Dummy so that I would have a "marker" to know
which sheets to delete later. I am sure there is a better way of doing that.
I tried using the insert after method on the worksheets in OpenFileName but
I get subscript out of range errors unless I use
workbooks(OpenFileName).open first.

I would appreciate any help.

Regards
Andrew



Sat, 28 Feb 2004 17:51:06 GMT  
 Copying Sheets from a closed workbook into an open workbook
Andrew,

Once you've opened the file, move all the sheets across to
the desired workbook (if you move all sheets, the workbook
will close), then delete the "dummy" sheet with something
like:

    Sheets(Array
("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")).Move _
        Before:=Workbooks("Book1").Sheets("Dummy")
    Sheets("Dummy").Delete

If your sheet names are constant, then you can specify
them in the array.  If they might vary then you will need
to do something a bit more clever.

The above will give a warning when you delete "dummy" (are
you sure?).  You can turn this off with an
 Application.DisplayAlerts = False
before the delete, but make sure you set it back to True
after.

HTH

Tim

Quote:
>-----Original Message-----
>Hi all,
>I  want to import all sheets from a closed workbook into
my open workbook
>and then delete all the existing sheets that were there
before the import.
>Here's what I have so far:

>Sub ImportData
>    Dim ImportWorkbook As Workbook
>    'Rename 1st Sheet to Dummy
>    ActiveWorkbook.Worksheets(1).Name = "Dummy"
>    'Use common dialog control to find required excel
workbook for import
>    Dim OpenFileName As String
>    frmProgressBar1.DlgImport.Flags = 0
>    frmProgressBar1.DlgImport.CancelError = True
>    frmProgressBar1.DlgImport.Filter = "Import
Workbooks  " &
>"(*.xls)|*.xls|All files " & "(*.*)|*.*"
>    frmProgressBar1.DlgImport.ShowOpen
>    OpenFileName = frmProgressBar1.DlgImport.filename

>I renamed the 1st sheet to Dummy so that I would have
a "marker" to know
>which sheets to delete later. I am sure there is a better
way of doing that.
>I tried using the insert after method on the worksheets
in OpenFileName but
>I get subscript out of range errors unless I use
>workbooks(OpenFileName).open first.

>I would appreciate any help.

>Regards
>Andrew

>.



Sat, 28 Feb 2004 22:13:34 GMT  
 Copying Sheets from a closed workbook into an open workbook
Hi Tim,
thanks for that. I was wondering if it was possible to copy the sheets
across from the source workbook to the destination workbook without actually
opening (.open) the source workbook. I saw an ADO method somewhere that
copies a range, but I wondered if there were a simpler method for copying
the entire sheets.

Regards
Andrew



Sun, 29 Feb 2004 02:57:47 GMT  
 Copying Sheets from a closed workbook into an open workbook
Andrew,

I think the simplest method is to open the source
workbook.  If you want to investigate using ADO have a
look at:

http://support.microsoft.com/support/kb/articles/q257/8/19.
asp

HTH

Tim

Quote:
>-----Original Message-----
>Hi Tim,
>thanks for that. I was wondering if it was possible to
copy the sheets
>across from the source workbook to the destination

workbook without actually
Quote:
>opening (.open) the source workbook. I saw an ADO method
somewhere that
>copies a range, but I wondered if there were a simpler
method for copying
>the entire sheets.

>Regards
>Andrew

>.



Sun, 29 Feb 2004 19:39:21 GMT  
 Copying Sheets from a closed workbook into an open workbook
Thanks again Tim. I will implement the .open approach for now, but I will
have a look at the ADO method.

Cheers
Andrew



Mon, 01 Mar 2004 21:54:14 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. copy sheets to new workbook, name new workbook by cell value then mail workbook

2. Macro to open a workbook, work in that workbook, than close the workbook (saved)

3. Copying sheets from a master workbook and paste special to 25 new workbooks

4. Copy data from a closes workbook to another workbook

5. Copy a whole sheet from a closed workbook

6. Disabling a closed workbook from an open workbook

7. How to count sheets in workbook WITHOUT open the workbook

8. Opening/closing workbook from UserForm in another workbook

9. Copy content of workbook into open workbook...

10. Closing 1 workbook closes all workbooks


 
Powered by phpBB® Forum Software © phpBB Group