Linked workbooks

Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

This will not only remove your links, but also *every* formula in the sheet!
It will leave only the data behind.
*No* further calculations can be made.

With the report open, do:
Ctrl A (selects the entire WS)
Right click in the selection and choose "Copy".
Right click again, and choose "Paste Special".
Click on "Values", then OK, then Esc.

Your report is now in the configuration, as if you keyed in every data
entry.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"AndyW" wrote in message
...
Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

Andy

Select all cells(CRTL + A) and Paste SpecialValuesOKEsc before saving As.

Gord Dibben Excel MVP

On Mon, 14 Jun 2004 18:39:37 -0700, "AndyW"
wrote:

Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

Thanks I had tried this and you are correct it does
eactly as you say, however I has to paste twice one for
data and one for format. What I forgot to mention was
that I have 10 charts on the next sheet which is
populated from the same source data.

I've found that selecting them all and then paste special
as a Metafile is the only way I can copy them without
maintaining the link.

I was going to ask if I can automate this but I think
I've just answered my own question. Because the template
is always used to create this report I could probably set
up a macro to do the copy and pasting into a new workbook.

Is there any way I can automate the naming of the new
workbook so that the operation of copying, opening a new
workbook, pasting the data and then saving as File Name
could be all one button press? The name would be
something lik QIS Report JUN04.XLS

thanks for the assitance

Andy W (Perth - Western Australia)
-----Original Message-----
This will not only remove your links, but also *every*
formula in the sheet!
It will leave only the data behind.
*No* further calculations can be made.

With the report open, do:
Ctrl A (selects the entire WS)
Right click in the selection and choose "Copy".
Right click again, and choose "Paste Special".
Click on "Values", then OK, then Esc.

Your report is now in the configuration, as if you keyed
in every data
entry.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all
may benefit!
==============================================


"AndyW" wrote in
message
...
Hi,

I have a report template that I have just developed which
is populated from 5 other spreadsheets that are emailed
to me. I save these to a folder and the report template
is linked to them so that when I open it it is update via
the links. I then use SaveAs to save the template as a
XLS report with the date appended. I then email the
report out to intended recipients.

The question I have is;
I no longer need the links in the sved report once the
report is completed and would like to delete them prior
to emailing. I obviously need to maintain the links in
the template (held in a different folder) for next months
report. The emailed report just need to maintain the
figures and not the links.

Any ideas or thoughts appreciated.

Andy (Perth WA)

.