Hi,
Im new to VBA. Im importing some data from Access to Excel for anaysis. I managed to come up with some VBA that automatically refreshes my data upon opening the workbook. This is called MyRefreshAll in the CODE shown below.
Now, the column names that come across from Acess are complicated and hard to interpret. For example my data for sales comes from AN ACCESS QUERY WHERE THE FEILD ANME IS CALLED "MaxOfSum"Sales.
I wanted to remove the words Max, Of, and Sum from all the feild names so that the Excel column header would just say Sales. To do this I recorded a macro that does a find and replace for the words Max, Of, and Sum and replaces them with nothing (takes them out)
But I have to run this everytime I open it. It should be easy enough to use VBS to fire this macro right after the data refresh fires (MyRefreshAll) but like I said Im new.
Could someone please have a look at the code below that Im using and tell me whats wrong or alternatively could you please re-write this code in the correct way?
With the code below the MyRefreshAll macro is working beautifully to refresh the data when the work book opens but the RemoveMaxOfSum which removes those words from the column headings does not do anything. Its jsut not working. What am I doing wrong here?
Code:
Private Sub Workbook_Open()
MyRefreshAll
RemoveMaxOfSum
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 RemoveMaxOfSum()
'
' RemoveMaxOfSum Macro
' Macro recorded 08/16/2010 by The City of Edmonton
'
'
ActiveWorkbook.RefreshAll
Thanks in advance for you help.
Kevin
End Sub
dBforums
>
VBA to fire macro upon opening
08-16-10, 17:33
Linear Mode
