Extracting Excel Formulas into Access
 

I'm writing a program in Access that reads an entire Excel spreadshee
and breaks each cell down into data components for storage in
database. From here, there are modules I've written that will read th
tables I've created and then recreate the Excel spreadsheet in Excel.
(The reason for all this fun is that, in my line of work, we run a lo
of reports that export themselves to Excel so the user can do whateve
they want with them. It's better than providing a static report i
Access.)

Anyway, the issue I'm having is trying to read the formula from a cel
in Excel. Programmatically, it looks like this (pseudo-coded):

DBField = xlSht.Range(CellContent).Formula

The problem is that, even if it's a formula, it returns the text of th
cell instead of the formula. For example, if A1 = 1000 and B1 = A1*2
then the above code would process cell B1 as 2000 instead of th
formula that generated the content.

The helpfiles say that I'm doing it correctly (even going as far as t
point out that it will return the "=" from the formula). However, th
help file and reality appear to be two different things.

Anyone have a good take on what's going on? And, even better, how do
get the formula and not the result?

Thanks,

~No

--
Novelt
-----------------------------------------------------------------------
Novelty's Profile: http://www.excelforum.com/member.php...fo&userid=1570
View this thread: http://www.excelforum.com/showthread.php?threadid=27232

Extracting Excel Formulas into Access
 

I get the formula string in Excel 2000 using a syntax such as:

activeworkbook.Sheets(1).cells(1,2).Formula

If the target cell contains a value, the code returns the value of the cell.
Whether the cell has a formula can be determined by:

activeworkbook.Sheets(1).cells(1,2).HasFormula

This returns TRUE or FALSE.

"Novelty" wrote:


I'm writing a program in Access that reads an entire Excel spreadsheet
and breaks each cell down into data components for storage in a
database. From here, there are modules I've written that will read the
tables I've created and then recreate the Excel spreadsheet in Excel.
(The reason for all this fun is that, in my line of work, we run a lot
of reports that export themselves to Excel so the user can do whatever
they want with them. It's better than providing a static report in
Access.)

Anyway, the issue I'm having is trying to read the formula from a cell
in Excel. Programmatically, it looks like this (pseudo-coded):

DBField = xlSht.Range(CellContent).Formula

The problem is that, even if it's a formula, it returns the text of the
cell instead of the formula. For example, if A1 = 1000 and B1 = A1*2,
then the above code would process cell B1 as 2000 instead of the
formula that generated the content.

The helpfiles say that I'm doing it correctly (even going as far as to
point out that it will return the "=" from the formula). However, the
help file and reality appear to be two different things.

Anyone have a good take on what's going on? And, even better, how do I
get the formula and not the result?

Thanks,

~Nov


--
Novelty
------------------------------------------------------------------------
Novelty's Profile: http://www.excelforum.com/member.php...o&userid=15701
View this thread: http://www.excelforum.com/showthread...hreadid=272321