Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    VBA to refresh data upon opening workbbok and VBA to automatically fire upon Refresh

    Can someone please help me? I am new to VBA and I have what is probably a very simple to solve problem. I want to refresh my data (from Access) automatically whenever the workbook is open and then I also want a code to fire automatically right after that whenever the data is refreshed...

    I have a DB in Access that I import into Excel for Analysis. One of the columns (column B) of data that imports is a field called "QUARTER" that field is populated by either a 1, 2, 3, or 4 (they represent a Quarter [either Q1, Q2, Q3, or Q4])

    I have writen the following code that will append the letter "Q" onto these numbers so that they read Q1, Q2, Q3, or Q4 instead of just 1,2,3,4.

    The code looks like this and works fine.

    Sub AddQ()

    Dim cell As Range
    For Each cell In Range("B2:B500")
    If cell.Value <> "" Then
    cell.Value = "Q" & cell.Value
    Else: cell.Value = cell.Value
    End If
    Next cell
    End Sub

    Now, I also have recorded a macro called RefreshAll which is simply a macro that presses the refresh button.

    The Macro code is in a module and it Looks like this:

    Sub RefreshAll()
    '
    ' RefreshAll Macro
    ' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
    '

    '
    ActiveWorkbook.RefreshAll
    End Sub


    Can someone please tell me how to make the refresh macro fire automatically when the workbook opens then have the addQ vba fire right after that upon data refresh.

    Please bear with me as I am new. Could please explain what operators I need to add to this code and where to add it and how to add it. Or please suggest any better code ideas that would work if this meathod is incorrect. I am flying by the seat of my pants and have never writen code before or used VBA before this so any help would be really great. Im getting nowhere online other than a suggestion to use a workbook_open () event and a workbook_calculation () event. Which is fine but I don't know how to write this and incorporate it into my code and I don't know how to incorporate these events into both my RefreshAll module and my AddQ vba.

    Please help!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    Hi,

    You might not even need VBA to do this. In the properties, you can set your Data Range or Pivot Table to automatically refresh when the workbook is opened. So that should be all you need.

    Can someone please tell me how to make the refresh macro fire automatically when the workbook opens
    Just so you know, when a workbook is opened the workbook's open event is raised. This means that you can use the Workbook_Open() event handler to execute code when the workbook opens. In the VBE's project explorer window, navigate to and double click on the ThisWorkbook module to open up its code pane. Above the code pane there are two dropdown boxes. In the left dropdown box choose Workbook. When you've done that the following code will automatically be put into the code module for you:
    Code:
    Private Sub Workbook_Open()
     
    End Sub
    You can then add code (such as a call to another procedure) into the event handler. For example:
    Code:
    Private Sub Workbook_Open()
        MsgBox "Welcome to my workbook"
    End Sub
    With this, everytime the workbook is opened you will get a message box.

    Hope that helps...

  3. #3
    Join Date
    Jun 2010
    Posts
    15
    Thank you so much for you response and for you continued help. Please bear with me as I mentioned I dont really know how the operators work when writing code.

    You might not even need VBA to do this. In the properties, you can set your Data Range or Pivot Table to automatically refresh when the workbook is opened. So that should be all you need.
    So can you explain how this is done. Im sorry I dont really know. When you say properties I went under "file" "properties" and thats not where it is. How would I set this so that my Access data is refreshed each time it opens as you say. If this dosn;t work let me ask something about your second piece of advice.

    Please clarify for me (sorry Im slow and new) if I wanted to use the "Workbook_Open()" code you suggest in the "ThisWorkbook module" procedure to refresh my data how would I add my RefreshAll macro to this.

    Heres the Code that appears as you say .

    Private Sub Workbook_Open()

    End Sub

    The heres the macro I have that refreshes all....

    Sub RefreshAll()
    '
    ' RefreshAll Macro
    ' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
    '

    '
    ActiveWorkbook.RefreshAll
    End Sub

    So I am assuming I would copy and past this code to refresh all between the Workbook_Open code as you say to look like this

    Private Sub Workbook_Open()
    Sub RefreshAll()
    '
    ' RefreshAll Macro
    ' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
    '

    '
    ActiveWorkbook.RefreshAll
    End Sub

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    Hi,

    So can you explain how this is done. Im sorry I dont really know. When you say properties I went under "file" "properties" and thats not where it is.
    Are you pulling in your data using a query table or a pivot table?
    For a query table, right click on the range > Data range properties >tick "Refresh Data on file Open".
    For a pivot table, right click on the pivot table > table options > tick "Refresh on Open".

    Please clarify for me (sorry Im slow and new) if I wanted to use the "Workbook_Open()" code you suggest ...
    You can either use:
    Code:
    Private Sub Workbook_Open()
        ThisWorkbook.RefreshAll
    End Sub
    Or, if you want to keep your separate procedure:

    Code:
    Private Sub Workbook_Open()
        MyRefreshAll
    End Sub
     
     
     
     
     
    Sub MyRefreshAll()
    '
    ' RefreshAll Macro
    ' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
    '
     
    '
    ActiveWorkbook.RefreshAll
    End Sub
    Note that I changed the name of your procedure - the workbook class already has a RefreshAll method, so we don't want any confusion.

    (As mentioned, you don't need to use VBA because you can set it to do this automatically within the pivot/query table options).
    Last edited by Colin Legg; 06-23-10 at 13:55.

  5. #5
    Join Date
    Jun 2010
    Posts
    15
    Are you pulling in your data using a query table or a pivot table?
    Its a pivot table.

    And thanks for showing me how to write the Refresh all one. Hopefully this works. Ill have to wait for some new data to come in to test it. But Im sure it will. You certainly seem to know what your talking about!!

    No that I have that code set up so that it refreshes all when the workboook is open...How do I tie my AddQ code to this so that right after the refresh all procedure fires (that is new data is refreshed) then the AddQ will go off?

    Thanks again.

  6. #6
    Join Date
    Jun 2010
    Posts
    15
    Just a reminder my AddQ code looks like this.

    Sub AddQ()

    Dim cell As Range
    For Each cell In Range("B2:B500")
    If cell.Value <> "" Then
    cell.Value = "Q" & cell.Value
    Else: cell.Value = cell.Value
    End If
    Next cell
    End Sub

    So I need it to work right after the refresh happens.

  7. #7
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    So I need it to work right after the refresh happens.
    Look in my post at how we called your MyRefreshAll procedure. Have a stab at calling your AddQ procedure on the next line.

  8. #8
    Join Date
    Jun 2010
    Posts
    15
    Hi Thanks, Just one thing though. is the "MY" "MyRefreshAll" included in this code intentionally. Is MY some kind of nessesary operator? Because my macro is actually calle RefreshAll. Not MyRefreshAll. Sorry, im a really green newby.

  9. #9
    Join Date
    Jun 2010
    Posts
    15
    ALL IN ALL WOULD IT LOOK LIKE THIS THEN?

    Private Sub Workbook_Open()
    MyRefreshAll
    End Sub
    __________________________________________________ _______________________




    Sub MyRefreshAll()
    '
    ' RefreshAll Macro
    ' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
    '

    '
    ActiveWorkbook.RefreshAll
    End Sub
    __________________________________________________ _________________________

    Sub AddQ()

    Dim cell As Range
    For Each cell In Range("B2:B500")
    If cell.Value <> "" Then
    cell.Value = "Q" & cell.Value
    Else: cell.Value = cell.Value
    End If
    Next cell
    End Sub

    The VBE automatically put those sepration lines in when I copied and pasted my codes. I don't know if they are correct of if my operators are messed up but can I please ask you...Does this LOOK right to you??? At first glance or do you see some problems with this one?

  10. #10
    Join Date
    Jun 2010
    Posts
    15
    Also, please let me just confirm. This code would all be entered into the "ThisWorkbook" modual correct?

    Thanks

  11. #11
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    Nearly, but you'd also need to call AddQ from the event handler too:

    Code:
    Private Sub Workbook_Open()
        MyRefreshAll
        AddQ
    End Sub


    The VBE automatically put those sepration lines in when I copied and pasted my codes. I don't know if they are correct
    Those procedure separators are perfectly fine and are automatically introduced to help to make the code easier to read. If you really dislike them then you can turn them off by VBE > Tools > Options > Editor Tab and untick "Procedure Separator".

  12. #12
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    Quote Originally Posted by Fxstc1340 View Post
    Also, please let me just confirm. This code would all be entered into the "ThisWorkbook" modual correct?

    Thanks
    That will be easiest for now because then you don't have to worry about procedural scope.

  13. #13
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    509
    Hi,
    , Just one thing though. is the "MY" "MyRefreshAll" included in this code intentionally. Is MY some kind of nessesary operator?
    I explained this earlier:
    Quote Originally Posted by Colin
    Note that I changed the name of your procedure - the workbook class already has a RefreshAll method, so we don't want any confusion
    It doesn't have to begin with "My" - just call it something different to RefreshAll.



    Without being too picky, there's one thing I would change in your AddQ code. I would specify which worksheet the range is on that should be looped through:
    Code:
    Sub AddQ()
        Dim cell As Range
     
        For Each cell In Worksheets("My Worksheet").Range("B2:B500")
            If cell.Value <> "" Then
                cell.Value = "Q" & cell.Value
            Else: cell.Value = cell.Value
            End If
        Next cell
     
    End Sub
    "My Worksheet" is the name of the worksheet which you'll have to change.

    The reason I suggest this is, with your original AddQ code, it relies on "My Worksheet" (or whatever its name is) being the ActiveSheet. By qualifying the Range property with the parent worksheet as I have done in red, nothing is left to chance.

  14. #14
    Join Date
    Jun 2010
    Posts
    15
    Thank you so much for all your continuous help with this! I think we got it. I will try your codes with a sample import right after I send this and I bet its going to work! If I could digitally upload a case of beer and send it to you I would! Thanks again.

  15. #15
    Join Date
    Jun 2010
    Posts
    15
    IT WORKED!!!!!!!Thanks Colin.

Posting Permissions

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