Closed Thread Icon

Topic awaiting preservation: Excel Export Question Pages that link to <a href="https://ozoneasylum.com/backlink?for=25425" title="Pages that link to Topic awaiting preservation: Excel Export Question" rel="nofollow" >Topic awaiting preservation: Excel Export Question\

 
Author Thread
WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 04-04-2005 17:01

I have a couple of excel sheets that have values that are dynamically linked from other Excel sheets and from a Project file, and I now want to take this excel file and send it to a client.

Is there an export ability for excel? or maybe a method to replace all of the formulas with the data that they currently represent?

The reason I need this is because the linked data will dissapear with an error warning if I send only the excel sheet. I would need to send all the linked files as well and this is not an option.

I used to love being MS dumb, and now it is a pain, and I just sit here wishing I still could be.

Dan @ Code Town

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana, USA
Insane since: Aug 2000

posted posted 04-04-2005 18:16

Dan,

There is no export function per se in Excel. However, if you use File-->Save As... and make the Save as Type *.csv, Excel should save only the values in the sheet. You can then open the *.csv as a different sheet and it will have only the values in it, no equations, formatting, or links.

Is that what you want to do?



.

-- not necessarily stoned... just beautiful.

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 04-04-2005 20:43

Well, I don't want the equations or the links, but I do need the formatting. So there is no may to simply press a button that replaces the functions with the current field value?

Dan @ Code Town

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana, USA
Insane since: Aug 2000

posted posted 04-04-2005 23:16
quote:
So there is no may to simply press a button that replaces the functions with the current field value?




Not that I know of. It was one of the features available in VisiCalc (and I think Lotus) that I really miss. In VisiCalc, you could highlight one or more cells and press the '#' key and it would drop the formulae and replace them with the calculated values. VisiCalc called this "pounding a formula".

I have looked in Excel for a feature like this for several years and never found one. In this kind of situation, I always end up creating a *.csv, creating a new sheet from the *.csv, and re-formatting the sheet the way I want it.

The other thing you might try is to make a copy of your spreadsheet, then Edit-Clear->Values all the values from the sheet. Call this the empty sheet. Then import the *.csv to a new sheet. Then copy the values from the new sheet to the empty sheet. Excel will retain your formatting in the empty sheet when you clear the values and when you copy the new values in, the sheet should look the way you want it with only values, no formulae.

Is this a particularly large spreadsheet or is it something you need to do more than once?



.

-- not necessarily stoned... just beautiful.

reitsma
Maniac (V) Mad Scientist

From: the smaller bedroom
Insane since: Oct 2000

posted posted 04-05-2005 01:36

Hi WarMage,

Your solution lies in cutting and pasting:

1) Start with existing document, A.
2) create new, blank document, B.
3) Select entire working area of A, and copy.
4) Select cell (A1) of document B, and then click Edit > Paste Special. In this option, choose Values.

..almost there...
5) go back to document A, and copy the same, entire working area.
6) go back to cell (A1) of document B, and then click Edit > Paste Special. This time, chose Formats.

Two caveats:

  • I'm not sure if step 5 is necessary, but it doesn't hurt to copy it again.
  • You may want to do the same thing again, but paste over the cell widths as well. (Another option in Paste Special).



That should give you the desired results.

DL-44
Maniac (V) Inmate

From: under the bed
Insane since: Feb 2000

posted posted 04-05-2005 01:56

^ Yeah, reitsma's got it. Not complicated at all.

Copy paste, copy paste.

Note: if you click in the corner of doc a, (not in the cells), copy, then click the corner in doc b and paste formats, the column width/row heights will be copied as well.

ZaddyDog
Obsessive-Compulsive (I) Inmate

From:
Insane since: Apr 2005

posted posted 04-05-2005 03:09

You don't need all those steps if you work with a copy of your file. no need for a new workbook.

Simply copy the selected range and then paste special values onto the selected cells.
that's all there is to it.. this way you preserve the source format without having to open a new file.

You can use ctrl A to select all the current worksheet., you can even select
all the tabs for al l the worksheets and do the entire workbook in one operation.

1) Start with existing document, A.
2) Select entire working area of A, and copy.
3) With current selection, Edit Paste Special Values (radio button).
done

if you have multiple worksheets and want to do every single worksheet in your workbook in one operation then starting in the first worksheet use the ctrl key and click on all the other worksheets tabs, then hit ctrl A and then move to step 2...the entire workbook is converted to values.

As a last step, go to the Edit menu and ensure Links are disabled.

(Edited by ZaddyDog on 04-05-2005 03:24)

WarMage
Maniac (V) Mad Scientist

From: Rochester, New York, USA
Insane since: May 2000

posted posted 04-05-2005 03:44

Wow, I never cease to be amazed at the amount of knowledge you all have.

Thank you very much for your help with this!

Dan @ Code Town

« BackwardsOnwards »

Show Forum Drop Down Menu