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

Posted on 2008-11-02
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

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 125 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

Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 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