Solved

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

Posted on 2008-11-02
11
573 Views
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
0
Comment
Question by:GRMPMS
  • 5
  • 5
11 Comments
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
No attached file...
0
 

Author Comment

by:GRMPMS
Comment Utility
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
Sample.xls
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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:
http://office.microsoft.com/en-gb/assistance/HA010872961033.aspx
Hope that helps
Patrick
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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.

Brad
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

0
 

Author Comment

by:GRMPMS
Comment Utility
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.

0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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.
0
 

Author Comment

by:GRMPMS
Comment Utility
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
0
 

Author Comment

by:GRMPMS
Comment Utility
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.
0
 
LVL 45

Accepted Solution

by:
patrickab earned 125 total points
Comment Utility
>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.
Patrick
0
 

Author Closing Comment

by:GRMPMS
Comment Utility
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
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
GRMPMS,
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:
http://www.experts-exchange.com/Community_Support/General/
Patrick
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now