Unanswered: Formula When Exported From Access Doesn't Work
We're developing a utility which exports from a query in an Access database into Excel, using the DoCmd.TransferSpreadsheet.
The query value is:
The formula displays in the cell, as though it's interpreted as being text. If it were preceded by an apostrophe (') I would understand.
If I edit the contents of the cell - merely adding a space at the end and removing the space - then exit the cell, the formula is treated as a formula and the cell displays either the asterisk (*) or nothing, as it should.
Does anyone have suggestions to correct this so that the formula is treated as a formula without having to touch it?
To get the formula to stick with this method, you'd have to open the spreadsheet and use a macro to set the formula to the value of the cell (c), i.e.
c.Formula = c.Value
That would need to be performed over all cells with formulas. Otherwise you can use a recordset and build the spreadsheet that way, would be more logic, but would give you more control. I don't see another way at this point.
I wasn't sure how to code "In Excel VBA, you could multiply the cell value by 1 which will ensure that it will become a number." (I'm too much a newbie). Was going to ask you how, but, between my posting and now I found out about the .HasFormula and then .Formula properties.
Here's the solution I came up with. The code follows the DoCmd.TransferSpreadsheet code:
- - - - - - -
'Set E Column Formula Property To The Formula
For i = 2 To iLastRow
strRow = Format(iLastRow, "0")
strRange = "E" & strRow
strTemp = "=If(D" & strRow & "<>(F" & strRow & "+H" & strRow & "+J" _
& strRow & "+L" & strRow & "),""*"","""")"
xlWSh.Range(strRange).Formula = strTemp
- - - - - - -
Rich - BTW: Would you please expand on your suggestion? Where do I place the code? What does the code look like? I'm sure this technique will come in handy some day, no sense passing up an opportunity to learn it now.