Hello,

I have a workbook open and I want to populate it with the content of another
xls file. How can I do that. I wan everything transferred: values, formulas,
embedded object, formatting, everyhting. I figured that I coould do a sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Why not just do a File Save As, within the existing .xls that you want
everything retained ?
(give it a new file name)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another xls file. How can I do that. I wan everything transferred: values,
formulas, embedded object, formatting, everyhting. I figured that I coould
do a sheet by sheet copy/paste (of course, enumerating the sheets and
chart sheets in the source document, recreating them in the destination
document and then doing the copy), but I am having a hard time copying
over anything else than values. Also, where are the macros stored? In each
sheet or are they workbook specific? Is there anything else to copy over
to make the opened document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one
that is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Hi LF,

To make a complete replica, you'd be better off simply copying the file with
Windows Explorer. Otherwise, if your source workook has multiple sheets with
formulae referring to different sheets, you're likely to end up with the
links to the original source workbook, not the copy.

Having said that, if you really want to copy & paste everything, use Ctrl-A
then Ctrl-C to copy the sourceworksheet, then Ctrl-V to paste it into the
target.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Max,
I already have the workbook opened where I want to bring things in (from a
file). I cannot use SaveAs.
Regards,
Levente

"Max" wrote in message
...
Why not just do a File Save As, within the existing .xls that you want
everything retained ?
(give it a new file name)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another xls file. How can I do that. I wan everything transferred:
values, formulas, embedded object, formatting, everyhting. I figured that
I coould do a sheet by sheet copy/paste (of course, enumerating the
sheets and chart sheets in the source document, recreating them in the
destination document and then doing the copy), but I am having a hard
time copying over anything else than values. Also, where are the macros
stored? In each sheet or are they workbook specific? Is there anything
else to copy over to make the opened document identic to the one I am
copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs on the source workbook and then open that. I need to work with
the one that is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Hello,

I am not sure I understand. When you copy over from one worksheet to
another, don't yopu simply copy the formulas? If I will co this copying for
all the sheets in the source workbook, shouldn't I recreate the exact
strucutre of the original workbook?

Also, is the Worksheet.Copy method working for copying from one workbook to
another? This supposedly copies everything.

Regards,
Levente


"macropod" wrote in message
...
Hi LF,

To make a complete replica, you'd be better off simply copying the file
with
Windows Explorer. Otherwise, if your source workook has multiple sheets
with
formulae referring to different sheets, you're likely to end up with the
links to the original source workbook, not the copy.

Having said that, if you really want to copy & paste everything, use
Ctrl-A
then Ctrl-C to copy the sourceworksheet, then Ctrl-V to paste it into the
target.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets
in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

If you do File Save As (for the "complicated" source .xls), you could then
build it up further from there. I'm presuming of course, that the "other"
workbook you have is almost like a "new" workbook which contains only some
work easily copied & pasted over into this "saved as" copy, in new sheets
for example. Anyway, this is the route I'd naturally take for such
instances.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LF" wrote in message
...
Max,
I already have the workbook opened where I want to bring things in (from a
file). I cannot use SaveAs.
Regards,
Levente

If you do File Save As (for the "complicated" source .xls), you could then
build it up further from there. I'm presuming of course, that the "other"
workbook you have is almost like a "new" workbook which contains only some
work easily copied & pasted over into this "saved as" copy, in new sheets
for example. Anyway, this is the route I'd naturally take for such
instances.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"LF" wrote in message
...
Max,
I already have the workbook opened where I want to bring things in (from a
file). I cannot use SaveAs.
Regards,
Levente

You say that you want "to make the opened document identic to the one I am
copying from".
If so then, create a copy of the file first, then open the copy:

name oldfile as newfile
workbooks.open newfile

However, if you really mean that you want to import all the sheets from the
oldfile into the newfile :
Dim SourceWB as workbook
Dim DestinationWB as workbook
dim WS as worksheet

set SourceWb =workbooks.open(oldfile)
set DestinationWb =workbooks.open(newfile)

for each ws in sourcewb.worksheets
WS.Copy After:=DestinationWb.Sheets(DestinationWb.sheets.c ount)
next

NickHK

"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Thanks Nick,

And this also answers my question if I can use the Workshhet.Copy method to
copy from one workbook to another. I will try this later today. Assuming I
do these steps:

1. Delete all chart sheets from the opened destination file
2. Delete all sheets from the destination file (except one, as it cannot be
left empty, exception thrown on deleting the last one).
3. Copy all sheets and then all chart sheets from the source file
4. Delete the one leftover sheet from the original content of the
destination

I guess that this should get me into the position where the content of the
destination is exactly like to content of the source. I mean values,
formulas, formatting (cell types, fonts, grouping, borders, etc.), embedded
charts and other COM objects (like embedded Word documents) in sheets are
all transferred. I really hope that this will work. In case there are linked
OLE objects, I assume that this would make the content of the destination
refer to the same sources of the links as in the source workbook. Is this
all correct?

Regards,
Levente


"NickHK" wrote in message
...
You say that you want "to make the opened document identic to the one I am
copying from".
If so then, create a copy of the file first, then open the copy:

name oldfile as newfile
workbooks.open newfile

However, if you really mean that you want to import all the sheets from
the
oldfile into the newfile :
Dim SourceWB as workbook
Dim DestinationWB as workbook
dim WS as worksheet

set SourceWb =workbooks.open(oldfile)
set DestinationWb =workbooks.open(newfile)

for each ws in sourcewb.worksheets
WS.Copy After:=DestinationWb.Sheets(DestinationWb.sheets.c ount)
next

NickHK

"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets
in
the source document, recreating them in the destination document and then
doing the copy), but I am having a hard time copying over anything else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not Sheet2 in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]

Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can I
adjust the formulas after copying. Any solution?

"macropod" wrote in message
...
Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not Sheet2
in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]

Can I copy several sheets at once, from one workbook to another, using
Worksheets.Copy? In this case, will the references to the formulas that are
in the copied group of sheets maintained correctly?

"macropod" wrote in message
...
Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not Sheet2
in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]

But as your other replies have indicated, there is no reason to do it this
way and you will make your like more difficult, re formulae pointing to the
wrong workbook.
Make a copy of the WB first, then open it. Either Excel.SaveAs or the VBA
"Name OldFile As NewFile".

NickHK


"LF" wrote in message
...
Thanks Nick,

And this also answers my question if I can use the Workshhet.Copy method
to
copy from one workbook to another. I will try this later today. Assuming I
do these steps:

1. Delete all chart sheets from the opened destination file
2. Delete all sheets from the destination file (except one, as it cannot
be
left empty, exception thrown on deleting the last one).
3. Copy all sheets and then all chart sheets from the source file
4. Delete the one leftover sheet from the original content of the
destination

I guess that this should get me into the position where the content of the
destination is exactly like to content of the source. I mean values,
formulas, formatting (cell types, fonts, grouping, borders, etc.),
embedded
charts and other COM objects (like embedded Word documents) in sheets are
all transferred. I really hope that this will work. In case there are
linked
OLE objects, I assume that this would make the content of the destination
refer to the same sources of the links as in the source workbook. Is this
all correct?

Regards,
Levente


"NickHK" wrote in message
...
You say that you want "to make the opened document identic to the one I
am
copying from".
If so then, create a copy of the file first, then open the copy:

name oldfile as newfile
workbooks.open newfile

However, if you really mean that you want to import all the sheets from
the
oldfile into the newfile :
Dim SourceWB as workbook
Dim DestinationWB as workbook
dim WS as worksheet

set SourceWb =workbooks.open(oldfile)
set DestinationWb =workbooks.open(newfile)

for each ws in sourcewb.worksheets
WS.Copy After:=DestinationWb.Sheets(DestinationWb.sheets.c ount)
next

NickHK

"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart sheets
in
the source document, recreating them in the destination document and
then
doing the copy), but I am having a hard time copying over anything else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the
opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Hi LF,

The way around this would be to create all the required worksheets in the
new workbook, with the same names as in the source workbook, then CUT (not
copy) & paste the source workbook's contents into the new workbook, after
which you close the source workbook without saving the changes.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can I
adjust the formulas after copying. Any solution?

"macropod" wrote in message
...
Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not Sheet2
in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]

You must have one sheet left in the Workbook when you do this!! You may need
to insert a blank sheet to start with.

Why not open both files, group all the sheets together by holding down the
Shift key and clicking on the last sheet, which will highlight every sheet
from the start to where you are clicking.

Then right click on the grouped sheets and select the Move or Copy option.

In the dropdown box, select the file name where you want the pages to go to.

Select before which current sheet where you want the sheets to appear. Click
OK.

Done.

Try it on a copy first just in case of problems.

Saruman


"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another xls file. How can I do that. I wan everything transferred: values,
formulas, embedded object, formatting, everyhting. I figured that I coould
do a sheet by sheet copy/paste (of course, enumerating the sheets and
chart sheets in the source document, recreating them in the destination
document and then doing the copy), but I am having a hard time copying
over anything else than values. Also, where are the macros stored? In each
sheet or are they workbook specific? Is there anything else to copy over
to make the opened document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a SaveCopyAs
on the source workbook and then open that. I need to work with the one
that is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Saruman,

I am only interested in doing this programatically.

Regards,
Levente

"Saruman" wrote in message
...
You must have one sheet left in the Workbook when you do this!! You may
need to insert a blank sheet to start with.

Why not open both files, group all the sheets together by holding down the
Shift key and clicking on the last sheet, which will highlight every sheet
from the start to where you are clicking.

Then right click on the grouped sheets and select the Move or Copy option.

In the dropdown box, select the file name where you want the pages to go
to.

Select before which current sheet where you want the sheets to appear.
Click OK.

Done.

Try it on a copy first just in case of problems.

Saruman


"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another xls file. How can I do that. I wan everything transferred:
values, formulas, embedded object, formatting, everyhting. I figured that
I coould do a sheet by sheet copy/paste (of course, enumerating the
sheets and chart sheets in the source document, recreating them in the
destination document and then doing the copy), but I am having a hard
time copying over anything else than values. Also, where are the macros
stored? In each sheet or are they workbook specific? Is there anything
else to copy over to make the opened document identic to the one I am
copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs on the source workbook and then open that. I need to work with
the one that is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Nick,

Please do not start a debate about whether is a reason to do it like I am
planning to do it. The workbook is ALREADY open at the time I am starting
this operation and I CANNOT CLOSE IT. Still, I need the good content in my
workbook (that is stored elsewhere at the time).

Best regards,
Levente

"NickHK" wrote in message
...
But as your other replies have indicated, there is no reason to do it this
way and you will make your like more difficult, re formulae pointing to
the
wrong workbook.
Make a copy of the WB first, then open it. Either Excel.SaveAs or the VBA
"Name OldFile As NewFile".

NickHK


"LF" wrote in message
...
Thanks Nick,

And this also answers my question if I can use the Workshhet.Copy method
to
copy from one workbook to another. I will try this later today. Assuming
I
do these steps:

1. Delete all chart sheets from the opened destination file
2. Delete all sheets from the destination file (except one, as it cannot
be
left empty, exception thrown on deleting the last one).
3. Copy all sheets and then all chart sheets from the source file
4. Delete the one leftover sheet from the original content of the
destination

I guess that this should get me into the position where the content of
the
destination is exactly like to content of the source. I mean values,
formulas, formatting (cell types, fonts, grouping, borders, etc.),
embedded
charts and other COM objects (like embedded Word documents) in sheets are
all transferred. I really hope that this will work. In case there are
linked
OLE objects, I assume that this would make the content of the destination
refer to the same sources of the links as in the source workbook. Is this
all correct?

Regards,
Levente


"NickHK" wrote in message
...
You say that you want "to make the opened document identic to the one I
am
copying from".
If so then, create a copy of the file first, then open the copy:

name oldfile as newfile
workbooks.open newfile

However, if you really mean that you want to import all the sheets from
the
oldfile into the newfile :
Dim SourceWB as workbook
Dim DestinationWB as workbook
dim WS as worksheet

set SourceWb =workbooks.open(oldfile)
set DestinationWb =workbooks.open(newfile)

for each ws in sourcewb.worksheets
WS.Copy After:=DestinationWb.Sheets(DestinationWb.sheets.c ount)
next

NickHK

"LF" wrote in message
...
Hello,

I have a workbook open and I want to populate it with the content of
another
xls file. How can I do that. I wan everything transferred: values,
formulas,
embedded object, formatting, everyhting. I figured that I coould do a
sheet
by sheet copy/paste (of course, enumerating the sheets and chart
sheets
in
the source document, recreating them in the destination document and
then
doing the copy), but I am having a hard time copying over anything
else
than
values. Also, where are the macros stored? In each sheet or are they
workbook specific? Is there anything else to copy over to make the
opened
document identic to the one I am copying from?

Because the destination workbook is already open, I cannot do a
SaveCopyAs
on the source workbook and then open that. I need to work with the one
that
is already opened.

Any help or guidance is greately appreciated, thank you.

Regards,
Levente

Hello all,

I managed to copy all worksheets and chart sheets. However, the formulas are
referring to the source file (as I was warned). I would like to iterate all
cells and update the formulas for each (seems simple, I only need to change
the text of the formulas and replace "[old_source_file.xls]" with ""). Can
this be done?

Also, can I modify each embedded chart object on each worksheet and change
it to not refer to the old workbook file (in case the chart was using data
from a different sheet). Where is the chart's data/formulas kept? Can I
accees the formulas?

Best regards,
Levente


"macropod" wrote in message
...
Hi LF,

The way around this would be to create all the required worksheets in the
new workbook, with the same names as in the source workbook, then CUT (not
copy) & paste the source workbook's contents into the new workbook, after
which you close the source workbook without saving the changes.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets? Can
I
adjust the formulas after copying. Any solution?

"macropod" wrote in message
...
Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example, the
copied formula will point to Sheet2 in the original workbook, not
Sheet2
in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi Levente,

A simple Find/Replace operation on each worksheet will suffice, as proposed.
Do note that, if your new workbook's sheets aren't named the same as in the
old one, the cross-sheet links will fail.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Hello all,

I managed to copy all worksheets and chart sheets. However, the formulas
are
referring to the source file (as I was warned). I would like to iterate
all
cells and update the formulas for each (seems simple, I only need to
change
the text of the formulas and replace "[old_source_file.xls]" with ""). Can
this be done?

Also, can I modify each embedded chart object on each worksheet and change
it to not refer to the old workbook file (in case the chart was using data
from a different sheet). Where is the chart's data/formulas kept? Can I
accees the formulas?

Best regards,
Levente


"macropod" wrote in message
...
Hi LF,

The way around this would be to create all the required worksheets in
the
new workbook, with the same names as in the source workbook, then CUT
(not
copy) & paste the source workbook's contents into the new workbook,
after
which you close the source workbook without saving the changes.

Cheers

--
macropod
[MVP - Microsoft Word]


"LF" wrote in message
...
Ooops. How can I get this to be correct? I mean, cannot a formula be
incorrect for a while until I also copy over the rest of the sheets?
Can
I
adjust the formulas after copying. Any solution?

"macropod" wrote in message
...
Hi LF,

I am not sure I understand. When you copy over from one worksheet to
another, don't you simply copy the formulas?

If you have a formula on Sheet1 that refers to Sheet2, for example,
the
copied formula will point to Sheet2 in the original workbook, not
Sheet2
in
the copy.

Cheers

--
macropod
[MVP - Microsoft Word]