• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

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

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
  • 5
  • 5
1 Solution
No attached file...
GRMPMSAuthor Commented:
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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

GRMPMSAuthor Commented:
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.

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.
GRMPMSAuthor Commented:
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
GRMPMSAuthor Commented:
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.
>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.
GRMPMSAuthor Commented:
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
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now