Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2010
    Posts
    15

    Unanswered: VBA to fire macro upon opening

    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

  2. #2
    Join Date
    Jun 2010
    Posts
    15

    thanks in advance for your help

    Sorry somehow the thanks in advance for your help, Kevin made its way into the code. This is a mistake. This is not part of the code. Im also new to posting threads. My appologies.

  3. #3
    Join Date
    Jun 2010
    Posts
    15
    Anyone? If anyone is familiar with VBA please help. Im kind of in a hurray tommorow is my last day for this.

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

    I assume you are using a query table.

    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.

  5. #5
    Join Date
    Jun 2010
    Posts
    15
    Thanks Colin, Does anyone know the SQL to perform the procedure Colin mentiones in his reply? ALIAS keyword? Where to enter this into the SQL and how it would look etc?
    Thanks for your help.

  6. #6
    Join Date
    Jun 2010
    Posts
    15
    Never mind I got it! 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
  •