Results 1 to 6 of 6
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    MSQuery and dynamic names (All?)

    I don't know how useful this will be, but thought I'd post it anyway as I haven't seen it mentioned anywhere before.

    If you are using MSQuery to retrieve data from an Excel workbook (or using the Import Data function rather than the New Database Query function), then any named ranges that have been defined dynamically will not appear as tables. For example, you have defined the name <code>Export</code>
    as:

    <code>=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A) ,COUNTA(Sheet1!$1:$1))</code>


    Export will not appear as a table in the dialog. (The same seems to be true trying to use VBA/ADO to retrieve this based on my quick tests).

    However, if you define an additional name, let's say <code>Export2</code>
    , as:

    <code>=EVALUATE(Export)</code>


    then this does appear in the MSQuery dialog and in the Import Data dialog as a valid table.

    Any thoughts/comments/problems appreciated. Note: I have only tested this in Excel 2002, but have seen it mentioned as an issue in prior versions - I do not know if it was fixed in subsequent versions.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: MSQuery and dynamic names (All?)

    This looks like a great tip!

    Shouldn't it be <code>=EVALUATE("Export")</code>?

    Note: it doesn't work in the Dutch language version of Excel, probably because the EVALUATE macro function has a localized Dutch name while MS Query expects the English name,

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MSQuery and dynamic names (All?)

    It seems I spoke too soon - although it was working perfectly yesterday, it will not work at all today, whether I use <code>EVALUATE(Export)</code> or <code>EVALUATE("Export")</code>! I am trying to determine why... <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MSQuery and dynamic names (All?)

    No, I cannot get it to work now, which makes me think I must have messed something up while testing it yesterday and probably overwrote the definition of Export2 with a range reference.
    So everyone please ignore my ramblings!! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: MSQuery and dynamic names (All?)

    It did surprise me, because Evaluate is in fact an Excel4 macro function, which is even more unlikely to be evaluated when you're extraction data from an Excel book without Excel itself.
    Would've been nice though!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: MSQuery and dynamic names (All?)

    I think I've gotten a bit carried away with EVALUATE since I realised that if you were doing dependent validation lists, you could use EVALUATE instead of INDIRECT and then your dependent lists could be defined dynamically. I guess I'll just have to accept the fact that it's not a cure for everything! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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