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!

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.

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:
You can then add code (such as a call to another procedure) into the event handler. For example:
With this, everytime the workbook is opened you will get a message box.

Hope that helps...

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.

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

Hi,

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

You can either use:
Or, if you want to keep your separate procedure:

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).

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.

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.

Look in my post at how we called your MyRefreshAll procedure. Have a stab at calling your AddQ procedure on the next line.

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.

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?

Also, please let me just confirm. This code would all be entered into the "ThisWorkbook" modual correct?

Thanks

Nearly, but you'd also need to call AddQ from the event handler too:



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

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

Hi,
I explained this earlier:
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:
"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.

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.

IT WORKED!!!!!!!Thanks Colin.