Topic awaiting preservation: Excel Export Question (Page 1 of 1)

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

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.

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

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.

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.

^ 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.

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)

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