Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    183

    Unanswered: Access to Excel problem

    Using the below code I'm insert a series o values into an excelsheet, amongst others from diverse variables and a recordset.

    Observe the last two inserts, to column 7 and 6. To column 7 I add a string perfectly, without a hitch. Yet, with column 6, I am not allowed to use = in the string. It exits with error 1004. If I remove the = and let it fill out the spreadsheet and manually add = to the string in excel, the function completes without any issues, so the string is correct.

    AFRUND is the danish version for the function ROUND

    Thanks in advance,

    Trin


    Code:
    With objActiveWkb
                                
    .Worksheets(6).Cells(fordarktal, 1) = kunst_udvalg!Medlemsnummer
    .Worksheets(6).Cells(fordarktal, 3) = kunst_udvalg!Medlemsnavn
    .Worksheets(6).Cells(fordarktal, 4) = kunst_udvalg!Points
    .Worksheets(6).Cells(fordarktal, 4).NumberFormat = "0"
    .Worksheets(6).Cells(fordarktal, 7) = "=(E" & Trim(Str(belobteller)) & "*" & kunst_udvalg!Points & ")/$D$" & Trim(Str(sumteller))
    .Worksheets(6).Cells(fordarktal, 6) = "AFRUND(G" & Trim(Str(fordarktal)) & ";2)"
    fordarktal = fordarktal + 1
    kunstnerteller = kunstnerteller + 1
    
    End With
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Without specifying the property Excel used the default property for the Cell (Value). If you are entering a formula you need to enter this into the Formula property explicitly.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2005
    Posts
    183
    Hey there PF..

    Care to explain hat a bit more? I'm not sure I follow.

    If I insert the string "AFRUND(12,2223;2)" into a cell, naturally it doesn't compute, as its basially a dumb string.

    If I add = to the string, and thus insert "=AFRUND(12,2223;2)" Access creates an error 1004

    If I let it insert the string with = but add this manually afterwards, I don' get an error.

    So from what I hear you say, I should then write

    .Worksheets(6).Cells(fordarktal, 4).formula = "=AFRUND(12,2223;2)"

    Am I right?

    Thanks for your help man... Another notch in my table on you.

    Trin

    edit: typos
    Last edited by Trinsan; 07-25-06 at 09:06.
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry - my mistake - I thought it wouldn't recognise the formula (thought it looked similar to a problem I had).

    I can recreate your error if use your code but don't get an error with
    ROUND(122223, 2)

    NOTE - no comma in the numeric value and comma instead of semi colon.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Oct 2005
    Posts
    183
    Man, you're a bloody genius... change the semicolon to a comma, and it works.

    Sometimes the use of commas and semi colons really confuse the heck out of me in Access.

    Thanks for the help,

    Trin
    IT squid: networks, servers, firewalls, routers and I dabble a little with SQL-server and Access as well....

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Trinsan
    Thanks for the help
    My pleasure - glad it worked
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    For those of us who use comma as decimalseparator, we cannot use comma as arguementseparator in the user interface. So, in Excel formulas, Access controlsources/expressions ... we have to use semicolon in the user interface. VBE is a bit more US style, and uncommon for us.
    Roy-Vidar

Posting Permissions

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