If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > VBA to refresh data upon opening workbbok and VBA to automatically fire upon Refresh

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
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.

Quote:
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...
__________________
Colin

RAD Excel Blog
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.

Quote:
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
Hi,

Quote:
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".

Quote:
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).
__________________
Colin

RAD Excel Blog

Last edited by Colin Legg; 06-23-10 at 12:55.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jun 2010
Posts: 15
Quote:
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
Quote:
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.
__________________
Colin

RAD Excel Blog
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jun 2010
Posts: 15
Also, please let me just confirm. This code would all be entered into the "ThisWorkbook" modual correct?

Thanks
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
Nearly, but you'd also need to call AddQ from the event handler too:

Code:
Private Sub Workbook_Open()
    MyRefreshAll
    AddQ
End Sub


Quote:
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".
__________________
Colin

RAD Excel Blog
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
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.
__________________
Colin

RAD Excel Blog
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Sep 2008
Location: London, UK
Posts: 508
Hi,
Quote:
, 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.
__________________
Colin

RAD Excel Blog
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
Join Date: Jun 2010
Posts: 15
IT WORKED!!!!!!!Thanks Colin.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On