Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting values from formulas | Excel Worksheet Functions | |||
Excel formulas in Access | Excel Worksheet Functions | |||
My formulas can't 'see' numbers imported from Access in Excel, even when the format is the same. | Excel Discussion (Misc queries) | |||
Extracting Excel data and uploading to access | Excel Programming | |||
Extracting Access data | Excel Programming |