Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Pendleton, Indiana, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hyperlinking a Query (Excel 2K)

    I have created a query that requires user input and then
    made it a hyperlink in Excel and when you click on the
    link it opens into a new workbook, I want the query to
    post the results on the same page that the query is.
    Thanks in advance.

    Trent

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

    Re: Hyperlinking a Query (Excel 2K)

    If you rightclick in the data area where the last results were put, you can choose "Refresh data".
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Pendleton, Indiana, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinking a Query (Excel 2K)

    The problem occurs when you click on the link it opens a new workbook and then prompts the user to enter in the information. I have checked all of the link properties and see no place to make it stay on that sheet. Thanks

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

    Re: Hyperlinking a Query (Excel 2K)

    My suggestion was NOT to use a hyperlink, but to "save" the query in the spreadsheet (as the properties button will enable you to select. This button shows on the final dialog of the query definition wizard). Then later you can simply rightclick the data area and choose refresh.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Pendleton, Indiana, USA
    Posts
    59
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hyperlinking a Query (Excel 2K)

    Is there an easy way to create a button that would tell the data to refresh - some of the users are well lets say not very computer literate.

    Thanks again

    Trent

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

    Re: Hyperlinking a Query (Excel 2K)

    Untested!

    This code should do it:

    Sub Refre######()
    Activesheet.querytables(1).Refresh
    End Sub

    Put this in a normal module.

    1 Now rightclick a toolbar, choose customize, click New
    2 enter a name for your new toolbar.
    3 Go to the middle Tab of the dialog, click "Macros", drag the custom button to your new toolbar.
    4 rightclick the new button, choose "Attach macro" and select the one you just made from the list.
    5 Change other things, like button face, description etcetera.
    6 Now go back to the first Tab of the dialog and click Attach.
    7 select your new toolbar from the lefthand drowdown and click copy.
    8 OK your way out and save your workbook.

    Now to avoid your user getting an old toolbar when you give out a new version, you must make sure the toolbar gets deleted when your file closes. Do that in the Thisworkbook module by adding this code:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    On Error Resume Next
    Application.CommandBars("YourToolbarName").Delete
    End Sub
    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
  •