Results 1 to 2 of 2
  1. #1
    Join Date
    May 2009
    Posts
    19

    Unanswered: Linking Access DB to Excel via MSQuery (VBA Question),

    Guys,

    I have a report that is loaded to an access database weekly, and then refreshed through a spreadsheet via MS Query. The report works fine, but I want to add a button to the spreadsheet that will refresh the report since I'm building it for a non-savvy report user. I have included a parameter in the spreadsheet that she will alter, and then want to place a button for her to click to refresh after she inputs the parameter of choice.

    Problem is, I keep getting debug errors when I try to run the report. I've used this bit of code before when connecting via an ODBC connection, but it keeps giving me fits when I get to the Query Table.Refresh pointed out below.

    Again, the query is connected to a standard access db. Any thoughts?


    Sub RunPlatformClientAcct()

    Sheets("Sheet1").Activate
    Range("C1").Select
    ActiveSheet.Calculate

    Sheets("Sheet1").Activate
    Range("C1").Select
    ----> Selection.QueryTable.Refresh BackgroundQuery:=False

    Sheets("Sheet1").Select
    End Sub

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    What error message do you get?

    One issue could be caused by using select/selection, which would be fixed by:
    Code:
    Sub RunPlatformClientAcct()
    
        Sheets("Sheet1").Calculate
        Sheets("Sheet1").Range("C1").QueryTable.Refresh BackgroundQuery:=False
    
    End Sub
    NB. If you are using XL2007 or later (you didn't specify) then the code will be different.


    Incidentally, there's no need to have a button here. If you have a range parameter set up, you can right click on the querytable and go to Parameters. You can then tick the "Refresh automatically when cell value changes" checkbox for that parameter.

Posting Permissions

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