Results 1 to 9 of 9
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query manager (97, 2000,xp,2003)

    Hi All,

    Anyone that uses queries (data, get external data, new database query) in Excel might benefit from this new utility.

    It enables you to edit the sql strings and connect strings of your queries. Also has an option to add a parameter to a query.

    Start from the Tools menu.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Query manager (97, 2000,xp,2003)

    Jan Karel,

    Nice utility. I have two requests:
    1. Could you add "Like" to the list of operators for a parameter?
    2. Would it be possible to have two buttons to close the Query Manager window: OK (or Save) and Cancel? Or else put up a dialog asking if changes should be saved. I was playing around with an existing query just to see how your add-in worked, and found I couldn't cancel the changes (not a real problem of course, I just closed the workbook without saving it.)
    TIA!

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

    Re: Query manager (97, 2000,xp,2003)

    Hi Hans,

    <<1. Could you add "Like" to the list of operators for a parameter?>>

    We're working on those, just as Between and other operators.

    <<2. Would it be possible to have two buttons to close the Query Manager window: OK (or Save) and Cancel? Or else put up a dialog asking if changes should be saved.>>

    Strange. Once you change something in one of the textboxes you should be asked for confirmation (and thus be able to cancel your changes). Works for me. I do agree a cancel button would be good: don't change anything and close the dialog without further action.

    Thanks for your comments.

    I was playing around with an existing query just to see how your add-in worked, and found I couldn't cancel the changes (not a real problem of course, I just closed the workbook without saving it.)
    >>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query manager (97, 2000,xp,2003)

    jan Karel,

    i have treied this on Excel 97 SR2 and received the error message 'Compile Error in hidden Module: ModQueryManager' when I attempted to select QueryManager from the the tools menu.
    Maybe it is jsut too old a version of 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: Query manager (97, 2000,xp,2003)

    You are right, we didn't test in XL97.

    See if this one does work.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query manager (97, 2000,xp,2003)

    Thank Jan Karel

    That installed fine.

    Now there is an error when you select the change dbase button. (on new/empty workbook)
    Run time Error '91':
    Object Variable or with Block variable not set.

    Where there is a query on the current sheet it works fine.

    The connection string however has truncated the last character off the password.

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query manager (97, 2000,xp,2003)

    Hi Jan Karel,

    Nice we add-in as I have a large volume of spread sheets attached to db's etc. I did run into one error - not sure if it was me or not. Run time 13, type mismatch when I tried to add a parameter to spead sheet that was linked to a query in an access db.
    Cheers

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

    Re: Query manager (97, 2000,xp,2003)

    Thanks, we'll look into this.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Query manager (97, 2000,xp,2003)

    Here is the latest version.

    Enjoy.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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