Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Excel automation failed

    What I wanted to do is to copy the value of cell E21 of worksheet “Op Pg 3” automatically to cell D8 ( or Cell (8,4) ) on worksheet “Op Pg 1”

    During Excel automation from MS Access, I got an popup window “Update Values:3” . When I cancelled the window, the cell D8 on worksheet “Op Pg 1” has an error message “#Name?”

    Here is some of the codes:
    Dim xlApp As New Excel.Application
    Dim xlWS As Excel.Worksheet
    .
    ‘ here cells of 2 and more worksheets are being formatted and populated with values

    xlApp.Sheets("Op Pg 1").Select
    xlApp.Cells(8, 4).Formula = "= Op Pg 3!E21" <---- error here

    Some experts please help. Thank you

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by big_mon View Post
    What I wanted to do is to copy the value of cell E21 of worksheet “Op Pg 3” automatically to cell D8 ( or Cell (8,4) ) on worksheet “Op Pg 1”

    During Excel automation from MS Access, I got an popup window “Update Values:3” . When I cancelled the window, the cell D8 on worksheet “Op Pg 1” has an error message “#Name?”

    Here is some of the codes:
    Dim xlApp As New Excel.Application
    Dim xlWS As Excel.Worksheet
    .
    ‘ here cells of 2 and more worksheets are being formatted and populated with values

    xlApp.Sheets("Op Pg 1").Select
    xlApp.Cells(8, 4).Formula = "= Op Pg 3!E21" <---- error here

    Some experts please help. Thank you
    This issue happens whern you use spacves in page names. I would recommnd that yse and underscode ( _ ) not a space.


    Like this:

    xlApp.Cells(8, 4).Formula = "= Op_Pg_3!E21"

    Since you have spaces in the page name, try this:

    xlApp.Cells(8, 4).Formula = "= [Op Pg 3[!E21"
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The advice is right but there's a typo:
    Code:
    xlApp.Cells(8, 4).Formula = "= [Op  Pg 3]!E21"

  4. #4
    Join Date
    Jan 2008
    Posts
    54
    Thank you for your replies.
    I did not get those errors I mentioned, however I got a new error message "Application defined or object-defined error" and cell(8,4) in "Op Pg 1" is blank.

    What does those errors mean? Did I miss out something in declaring DIM or something?

Posting Permissions

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