Results 1 to 8 of 8
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DAO Error with dynamic range (2000 sp-3/ 98SE)

    I'm using DAO from Word to access data in a XL workbook. It's taken ages to track down the source of the error below, but I finally nailed it to the use of an Excel Name, defined as:
    ofst=OFFSET(sqr,1,0,COUNTA(INDEX(sqr,0,1))-1,1)

    in a simple workbook that contains (only) two other Names:
    sqr=Sheet1!$A$1:$B$20
    head=Sheet1!$A$1:$B$1

    I thought it might be overlapping Name ranges causing the problem, but it definitely seems to be caused by the presence of "ofst". For some reason this is screwing up the DAO. Apart from getting rid of OFFSET formulas for Name definitions, does anybody have any other suggestions please?

    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    I doubt that DAO "understands" dynamically defined ranges, so you'll probably have to define fixed ranges, or just a worksheet.

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    I probably should have explained a little more. The DAO.Database can be opened OK in all instances. The query I'm running involves ONLY the "simple" named range "sqr". It works fine if I delete "ofst", but I get the error if "ofst" even exists. I'm not actually asking DAO to do anything with, or even acknowledge the existence of, "ofst".

    Another thing I noticed just now:
    If the XL workbook is OPEN then the DAO proceeds smoothly in all cases. It only gets "picky" when the workbook is closed in XL.

    Alan

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    Perhaps one of our Excel gurus can shed more light on this.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    Isn't OFFSET a 'volatile' function that cannot be evaluated if the workbook is closed? If so, that could be the source of the problem - the query engine is encountering an error in evaluating "ofst" as it tries to access the workbook, even though the query isn't trying to use that range... I will mess around (technical term) and see if I can recreate that problem.

    If it works when the workbook is open, would it be feasible to include a line in your Word VBA routine to check if the required XL file is open, and open it if it is not already, and then perhaps close it again once the query has run?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    Have you considered using ADO instead of DAO? It might not show the issue?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    Hi Dean

    Yes, what you say makes good sense and sounds like the most logical source of the problem. Without having thought of that, I may have hit on a way to solve it. Since I'm managing all of the named ranges programatically, I can (re)evaluate the OFFSET expressions within VBA code and reset the value of the "ofst"-style ranges as more conventional (static) references, as needed. Once the workbook is closed, all ranges should be in "readable" form for the Jet engine.

    I'll give that a shot and report back. Thanks for pointing that out.

    Alan

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DAO Error with dynamic range (2000 sp-3/ 98SE)

    Jan & Hans

    I appear to have solved this by ensuring that the culprit range names are defined as static references when the workbook closes... a clunkier and less desirable solution, but at least it allows DAO to function.

    Alan

Posting Permissions

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