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?
Private Sub Workbook_Open()
' RefreshAll Macro
' Macro recorded 06/23/2010 by The City of Edmonton Refreshes Data from Access
' RemoveMaxOfSum Macro
' Macro recorded 08/16/2010 by The City of Edmonton
Thanks in advance for you help.
You don't need VBA to refresh the data when the workbook opens. Right click on the query table > data range properties > tick "Refesh data on open file".
You shouldn't need VBA to sort out the field headers either. Right click on the query table > edit query > click on the SQL button. You should be able to use the ALIAS keyword so that the query returns the field headers you need? My SQL knowledge is a definite 1/10 so if you are struggling with it then I'm sure one of the other members will able to help you.