PDA

View Full Version : Is this a good way to export what is in a cell?



ronjon65
11-09-2011, 08:26 AM
I want to export the cell (which may contain both numbers and formulas) to another sheet. I want to then read that back in later.

The problem:
- If I export as values, then I use the formula information
- If I export using standard copy/paste, then I will create a link to the filename. I may not always read back into the exact filename.

One Solution:
- Export with : Cells(1, 3) = "'" & Cells(1, 1).Formula
Basically, the cell becomes commented out. It retains values and formulas, but just has a ' in front.
- Import with : Cells(1, 1) = Cells(1, 3).Formula
This action automatically strips the comment out and everything seems to be preserved.

I simplified the example for discussion purposes, but the logic would the same.

Issues:
- If you had a leading ' in the original cell, this would have been lost when reading the data back in. Not such a big deal though. Perhaps there is a way to detect (and adjust) in the event of a leading '.
- If you read the data back in and the sheet does not exist (ref from a formula), then a pop up window will ask for the link. Could this be deal with easily via some form of event handling?

mdmackillop
11-09-2011, 11:58 AM
This should work with Constants or Formulae
Sheets(2).Cells(5, 5).Formula = ActiveCell.Formula