Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Unanswered: VBA to refresh upon open not running

    I have the simple VBA code

    Private Sub Workbook_Open()
    ThisWorkbook.RefreshAll
    End Sub

    to refresh the connections from my Access queries, but it's not refreshing anything when I open. I have to go to Developer, VB and hit Run to get it to work. Am I doing something wrong?

    I know I could click on the Refresh under connection properties, but I have mulitple users importing into the DB and I'm using excel as the template for the output. They need to be able to save after each workbook refresh to maintain that data for any particular customer (and then start all over again with the importing for a different customer). We're not Excel experts by far, so I figured I'd save the template as an xlsm and they can save after the run as an xlsx.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    For Excel 2007+ there's no VBA needed...

    On the ribbon go to the Data tab.
    On the data tab click Connections
    Open up the properties for each connection
    On the Usage tab check the box labelled "Refresh data when opening the file"
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2013
    Posts
    4
    I don't want the data to refresh upon opening once I have an individual customer saved - I need to keep that output intact, as the information in the DB changes constantly. Are there any other workarounds for this? The VBA seemed the best route, it just isn't running on opening.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I have the simple VBA code ... to refresh the connections from my Access queries, but it's not refreshing anything when I open
    I don't want the data to refresh upon opening
    I'm afraid you've confused me a bit here... Do you want the refresh all command to run when the spreadsheet is opened or not? Note that with the method I showed you it will only occur on the initial opening of the workbook i.e. if a user closes the spreadsheet and re-opens it will refresh.
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2013
    Posts
    4
    I want the template to refresh upon opening (in the VBA case, it's the xslm). I do not want the individual customer data (the xlsx) to refresh.

    The way it breaks down is as follows:

    User enters customer info in DB, then closes.
    User opens template and data is refreshed to show output for the customer they just entered.
    User saves customer info (this should NOT refresh).

    Then the cycle starts over for a new customer. I'm looking for a way the user can open the template and refresh automatically, then save the customer info without having to unclick the refresh properties each and every time. It should be as seamless and easy as the steps above for the user, as they are more at a beginner level with Excel.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How does the template know which customer the user just entered?
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2013
    Posts
    4
    Each user will have their own copy of the DB and template. They will only enter one customer at a time, then retrieve the output and save and start over.

    Is there any way to keep the output without refreshing, or will I just have to teach them how to refresh the VBA?

Posting Permissions

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