Transferring formulas from Access to Excel

[mophead]

I am doing a database application in Access which entails having to transfer a lot of information, including formulas from Access to Excel.
I envision I will be running into a lot of problems, but here is one I can't figure out. The portion of code below shows where the problem develops.
I am looping through a table and transferring the values from the "formula" field into cells. The "formula" field in the Access table is defined as text. When I run the code everything works just fine...the workbook opens and transfers the information blah blah...
BUT, for some reason the formulas are visible in the cell. Only when I place the cursor in the cell and hit the enter keys does it actually calculate the formula. Now this is weird, because it tells me that the formula is correct, but for some reason it is as the cells are waiting for something before actually calculating the formulas.
I tried replacing the MySet!Formula reference with the actual string for the formula so that the code line would read the following instead:
.Cells(CurrRow, 2).Value = "Utilities*3"
And it WORKED, the formula was transferred and calculated automatically...Beats ME!
I even tried setting MySet!Formula to a declared string variable and use the string variable instead, but it still didn't work.
Any help would be appreciated.

Do Until MySet.EOF
With MainWkSh
.Rows(CurrRow).Insert
.Cells(CurrRow, 1).Value = MySet!ProcessDataName
.Cells(CurrRow, 1).Name = MySet!ProcessDataName
.Cells(CurrRow, 1).Font.Italic = True
.Cells(CurrRow, 1).Font.Bold = False
.Cells(CurrRow, 2).Value = MySet!Formula
End With
'-- Move to next record
MySet.MoveNext
'-- Increment Row
CurrRow = CurrRow + 1
Loop

 

[JoeMiller]

Maybe try telling excel to do a recalculation of the sheet, as in hitting F9 on the keyboard? Joe Miller
joe.miller@flotech.net

 

[mophead]

I tried the recalculation thing, but it didn't work. I tried doing it manually (i.e. pressing F9) and not a thing happened!
Any more suggestions

 

[MichaelRed]

Being a big (NOT) fan of Excel, I'm not able to help. I WOULD like to know/understand more about why this type of operation is useful or necessary. All of my work with excel has been either for getting 'information' from them as sources or sending data to specific ranges for the generation of "Management" reports (the ones they e-mail back and forth) with charts. Really the only reason was for them ("Management" to be able to have the Charts in the e-mail). Other uses for excel are interesting, but not (yet) useful for me.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[MichaelRed]

OOps. I do have a CLUE (maybe?). If the dest cell is formatted as text, formulas entered into the cell do not calculate, even when a "formula" is entered. The same cell, if "edited" with the formula residing as "TEXT" will go ahead and calculate.

For a SIMPLISTIC example, Open a new worksheet. Pick any Cell (they are all blank) and format it as text. enter the string =5+1 in the cell and move the focus away from the cell. IT SHOWS =5+1 !!!!!!

Go Back to the cell as many times as you want and shift the focus away. It STILL SHOWS =5+1 !!!!!!

Go back and change the format to "general". "Edit" the cell (hit F2, Home, Return) Presto Magico, the darn thing NOW shows the 'results' of =5+1 6

My GUESS is that since you are transfering TEXT (string) information to the cell, it "internally" assumes the format of "text" - which 'doesn't compute'!

Beyond this, I'm strictly speculating. MAYBE if you set the cell format to general AFTER yo placed the formula text in it and did the re-calc thinggy it would work?
MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[mophead]

Yes, what you said is true, but when the formulas are transferred to the cells, they are automatically set to "General" number format. So that's not the problem either. And just for the heck of it, I still entered code to change the number format to "General" and recalculate and nothing happened (as I suspected). Any more suggestions, or is there and EXCEL EXPERT somewhere out there taht could help?

 

[THoey]

For an Excell expert, you are going to have to go to an Excell or MS Office forum. I know there is at least one here... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.