How to run a macro (saved as .bas)  at startup of a generic Excel file

Posted on 2008-11-02
Medium Priority
Last Modified: 2011-10-19
I am downloading data from Oracle, generating pivot table and then producing graphs. I need to automate the full cycle (from the user initiation - opening the excel with grphs).  I have the macro written to generate the graphs within excel. The problem is the macro is local to the excel, the generic download excel data will not have the visibility to this macro. Secondly the macro should automatically run on the generated generic file and product the graphs automatically. The attached file has the Macro
Question by:GRMPMS
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
LVL 45

Expert Comment

ID: 22860546
No attached file...

Author Comment

ID: 22860568
Sorry, The excel file herwith attached, with sample data, charts, Macro all in same sheet.
Next time arond the data will be downloaded to a different file, but need the same macro executed automatically

Best Regards
LVL 45

Expert Comment

ID: 22860613
Place the VBA code in Personal.xls and it will be available by all Excel files. The location of Personal.xls will vary according to your installation. Mine (Excel 2002) is located in C:\Program Files\Microsoft Office\Office10\XLStart\personal.xls
Have a look at this link for instructions on how to create Personal.xls:
Hope that helps
Supports up to 4K resolution!

The VS192 2-Port 4K DisplayPort Splitter is perfect for anyone who needs to send one source of DisplayPort high definition video to two or four DisplayPort displays. The VS192 can split and also expand DisplayPort audio/video signal on two or four DisplayPort monitors.

LVL 81

Expert Comment

ID: 22862180
I generalized your macro so it will work with worksheets having different names and different amounts of data. I also eliminated the statements recorded by the macro recorder that don't do anything beneficial. Finally, I turned off screenupdating so your macro will run faster and without flickering.

I agree with Patrick that a good place to store the macro would be in your Personal.xls workbook.

Sub StartMac2()
' StartMac Macro
'   Based on macro recorded 02/11/2008 by Aslam Hameed
Dim rg As Range
Dim sSheet As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Set rg = [A1].CurrentRegion
Set rg = Intersect([A:F], rg)
sSheet = rg.Worksheet.Name
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
    sSheet & "!" & rg.Address).CreatePivotTable TableDestination:="", TableName:= _
    "PivotTable1", DefaultVersion:=xlPivotTableVersion10
Set ws = ActiveSheet
ws.PivotTableWizard TableDestination:=ws.Cells(3, 1)
With ActiveSheet.PivotTables("PivotTable1")
    .PivotFields("Customer/Consignee").Orientation = xlPageField
    .PivotFields("Customer/Consignee").Position = 1
    .PivotFields("Inv Year").Orientation = xlRowField
    .PivotFields("Inv Year").Position = 1
    .PivotFields("Inv Month").Orientation = xlRowField
    .PivotFields("Inv Month").Position = 2
    .AddDataField .PivotFields("Total InvQty MT"), "Sum of Total InvQty MT", xlSum
    .PivotSelect "", xlDataAndLabel, True
    .Format xlReport1
    .AddDataField .PivotFields("Qty early"), "Sum of Qty early", xlSum
    .AddDataField .PivotFields("Qty late"), "Sum of Qty late", xlSum
End With
With Charts.Add
    .SetSourceData Source:=ws.Range("E5")
    .Location Where:=xlLocationAsNewSheet
    .ChartType = xlLineMarkers
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveWorkbook.SaveAs Filename:="C:\Documents and Settings\Aslam\My Documents\Sample.xls"
    .PivotLayout.PivotTable.PivotFields("Customer/Consignee").CurrentPage = "ManChee EXT"
    .PivotLayout.PivotTable.PivotFields("Customer/Consignee").CurrentPage = "(All)"
End With
Application.ScreenUpdating = True
End Sub

Open in new window


Author Comment

ID: 22865032
Thank You very much Patrick and Brad. This solved the problem for me. Patrick's Personal MAcro's and your modified script just did exactly what I needed. However I have one more issue onthe same pivot table.

I noticed the month field is sorted in a randomly order, how can i force pivot table creation routine to stick to the pre-sorted data I have.

LVL 45

Expert Comment

ID: 22865229
Select/Highlight the rows of the Pivot Table and use Data/Sort to obtain the order you want. If you have a special order or sorting then include that in the Pivot Table data and Sort the Pivot Table by that data.

Author Comment

ID: 22865438
If you see my sample table attached in previous correspondance,  the data is as follows, I cannot enforce a sorting witch will be alphebetical sort. If I introduce a concatenated column like YYYY-MM for the purpose of sorting pivot table it, but when I hide that column, it reverts back to randomly sorted list..

Inv Year      Inv Month      Total InvQty MT
2005      JANUARY        20
2005      FEBRUARY       19
2005      AUGUST         19
2005      OCTOBER        21
2006      DECEMBER       36

Author Comment

ID: 22915680
The answers to the my original question was answered, with the help of the feedback from Patrick I did the follwoing
Generate the output to a standard excel file. Once the file is generated, within the same program open a template excel file which will run the Macro against the generated file and produce the graph and close the template file within the macro.
However my second question to sort by month was not satisfactory, but I did managed by sqeezing a date field YYYY-MM format to force the sorting.
LVL 45

Accepted Solution

patrickab earned 375 total points
ID: 22915721
>with the inputs from experts I arrived at the final solution
If that is the case then why not simply award the points accodingly. After all the opposite - "...without the inputs from experts I would not have arrived at the final solution" must apply.
There is more often than not a case for generosity...particularly when it doesn't cost you anything to be generous.

Author Closing Comment

ID: 31512449
You deserve full marks, 125, I read it as 20 in the notes. Still getting around this forum, am a first timer. Please bare with me. Now I can't find the exact place to offer the reward points
LVL 45

Expert Comment

ID: 22915806
Thank you for that. However, that was not what I was looking for. Both Brad and I made contributions both of which I believe helped you solve the problem. If that is the case then perhaps it would have been appropriate to split the points between us rather than award the points only to me - which is a bit embarrassing to me as Brad made what I would thought was a useful contribution.
The call is of course yours but if you would like the question re-opened so that you can re-apportion the points then you can ask a free question, requesting that, here:

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question