We're developing a utility which exports from a query in an Access database into Excel, using the DoCmd.TransferSpreadsheet.

The query value is:
=if(d2<>(f2+h2+j2+l2),"*","")

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?

Howdy. In Excel VBA, you could multiply the cell value by 1 which will ensure that it will become a number.

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.
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.

Regards,

Ax

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
Next i
- - - - - - -

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.

LOL - While I was busy getting my code working it looks like Ax238 was on the same trail that I was discovering. Thanks.

Glad you were able to get it working!