Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11

    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:
    =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?

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. In Excel VBA, you could multiply the cell value by 1 which will ensure that it will become a number.
    Last edited by shades; 07-06-09 at 13:42.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    May 2009
    Posts
    258
    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.
    Code:
    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.

    Regards,

    Ax

  4. #4
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    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.

  5. #5
    Join Date
    Jun 2009
    Location
    Rochester NY
    Posts
    11
    LOL - While I was busy getting my code working it looks like Ax238 was on the same trail that I was discovering. Thanks.

  6. #6
    Join Date
    May 2009
    Posts
    258
    Glad you were able to get it working!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •