Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Hi All, I wrote a macro that builds a Pivot table and when I open the excel file it opens ok. Now trying to add any other line of code in the macro I'm no more able to reopen it because it crashes. How is it possible? what can I do to understand how to proceed? Thanks in advance to who will read this request of help. Regards Scardy -- scardy ------------------------------------------------------------------------ scardy's Profile: http://www.excelforum.com/member.php...o&userid=33589 View this thread: http://www.excelforum.com/showthread...hreadid=535478 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Scardy,
You will probably want to post your macro code. It will help others help you. "scardy" wrote: Hi All, I wrote a macro that builds a Pivot table and when I open the excel file it opens ok. Now trying to add any other line of code in the macro I'm no more able to reopen it because it crashes. How is it possible? what can I do to understand how to proceed? Thanks in advance to who will read this request of help. Regards Scardy -- scardy ------------------------------------------------------------------------ scardy's Profile: http://www.excelforum.com/member.php...o&userid=33589 View this thread: http://www.excelforum.com/showthread...hreadid=535478 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Yes Dominic, I want to add my macro code and also describe better what I want to obtain. 1) First I wrote a TEMPLATE.XLS with this macro code inside: Sub Auto_open() If Not SheetExists("Summary Chart") Then Sheets("Defects").Select ActiveWorkbook.Names.Add Name:="TotData", RefersToR1C1:= _ "=OFFSET(Defects!R1C1,0,0,COUNTA(Defects!C1),3 0)" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TotData").CreatePivotTable TableDestination:="", TableName:="PivotTable1" _ , DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Charts.Add ActiveChart.Location Whe=xlLocationAsNewSheet ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _ PivotTable.PivotFields("NAME"), "Sum of NAME", xlSum With ActiveChart.PivotLayout.PivotTable.PivotFields("Su m of NAME") .Caption = "Count of PTRs" .Function = xlCount End With With ActiveChart.PivotLayout.PivotTable.PivotFields("PH ASEFOUND") .Orientation = xlColumnField .Position = 1 End With With ActiveChart.PivotLayout.PivotTable.PivotFields("AD DDATE") .Orientation = xlRowField .Position = 1 End With Sheets("Chart1").Select Sheets("Chart1").Name = "Summary Chart" Sheets("Sheet4").Select Sheets("Sheet4").Name = "Summary Table" Sheets("Defects").Select Range("A1:AD1").Select Selection.Font.Bold = True Selection.AutoFilter Sheets("Summary Chart").Select Else Sheets("Summary Chart").Activate End If End Sub Function SheetExists(SheetName As String) As Boolean ' returns TRUE if the sheet exists in the active workbook SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets(SheetName).Name) 0 Then SheetExists = True Exit Function End If NoSuchSheet: End Function 2) Then I run a class java that copies (also its macro) this TEMPLATE.XLS into PIPPO.XLS. Here the code: Workbook workbook = Workbook.getWorkbook(new File("template.xls")); //* To obtain a writable (output) version of this spreadsheet, a copy must be made with possibility to copy macro WorkbookSettings ws = new WorkbookSettings(); ws.setPropertySets(true); WritableWorkbook copy = Workbook.createWorkbook(new File("PIPPO.xls"), workbook, ws); 3) When I run PIPPO.XLS it runs OK .... BUT ... if I add any line of code in the initial macro (for ex. Sheets("Sheet1").Select ) of TEMPLATE.XLS, then I copy it running the java class and then I open the produced PIPPO.XLS it fails!! EXCEL crashes!!! I'm getting crazy because I don't understand why? Thanks for everybody who can suggest me something Regards Scardy -- scardy ------------------------------------------------------------------------ scardy's Profile: http://www.excelforum.com/member.php...o&userid=33589 View this thread: http://www.excelforum.com/showthread...hreadid=535478 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
Scardy,
Do you need to do what you are trying to do WITHIN the first macro? If not, why don't you create a separate module/macro to accomplish what you are attempting. Is that possible? "scardy" wrote: Yes Dominic, I want to add my macro code and also describe better what I want to obtain. 1) First I wrote a TEMPLATE.XLS with this macro code inside: Sub Auto_open() If Not SheetExists("Summary Chart") Then Sheets("Defects").Select ActiveWorkbook.Names.Add Name:="TotData", RefersToR1C1:= _ "=OFFSET(Defects!R1C1,0,0,COUNTA(Defects!C1),3 0)" ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "TotData").CreatePivotTable TableDestination:="", TableName:="PivotTable1" _ , DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select Charts.Add ActiveChart.Location Whe=xlLocationAsNewSheet ActiveChart.PivotLayout.PivotTable.AddDataField ActiveChart.PivotLayout. _ PivotTable.PivotFields("NAME"), "Sum of NAME", xlSum With ActiveChart.PivotLayout.PivotTable.PivotFields("Su m of NAME") .Caption = "Count of PTRs" .Function = xlCount End With With ActiveChart.PivotLayout.PivotTable.PivotFields("PH ASEFOUND") .Orientation = xlColumnField .Position = 1 End With With ActiveChart.PivotLayout.PivotTable.PivotFields("AD DDATE") .Orientation = xlRowField .Position = 1 End With Sheets("Chart1").Select Sheets("Chart1").Name = "Summary Chart" Sheets("Sheet4").Select Sheets("Sheet4").Name = "Summary Table" Sheets("Defects").Select Range("A1:AD1").Select Selection.Font.Bold = True Selection.AutoFilter Sheets("Summary Chart").Select Else Sheets("Summary Chart").Activate End If End Sub Function SheetExists(SheetName As String) As Boolean ' returns TRUE if the sheet exists in the active workbook SheetExists = False On Error GoTo NoSuchSheet If Len(Sheets(SheetName).Name) 0 Then SheetExists = True Exit Function End If NoSuchSheet: End Function 2) Then I run a class java that copies (also its macro) this TEMPLATE.XLS into PIPPO.XLS. Here the code: Workbook workbook = Workbook.getWorkbook(new File("template.xls")); //* To obtain a writable (output) version of this spreadsheet, a copy must be made with possibility to copy macro WorkbookSettings ws = new WorkbookSettings(); ws.setPropertySets(true); WritableWorkbook copy = Workbook.createWorkbook(new File("PIPPO.xls"), workbook, ws); 3) When I run PIPPO.XLS it runs OK .... BUT ... if I add any line of code in the initial macro (for ex. Sheets("Sheet1").Select ) of TEMPLATE.XLS, then I copy it running the java class and then I open the produced PIPPO.XLS it fails!! EXCEL crashes!!! I'm getting crazy because I don't understand why? Thanks for everybody who can suggest me something Regards Scardy -- scardy ------------------------------------------------------------------------ scardy's Profile: http://www.excelforum.com/member.php...o&userid=33589 View this thread: http://www.excelforum.com/showthread...hreadid=535478 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table
I'd like to add it in the initial macro. Just to test I also created a different new module/macro that I want to run after the creation of the PIPPO.XLS file and again Excel crashes. Regards Scardy -- scardy ------------------------------------------------------------------------ scardy's Profile: http://www.excelforum.com/member.php...o&userid=33589 View this thread: http://www.excelforum.com/showthread...hreadid=535478 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Linking to a pivot table from a formatted invoice | Links and Linking in Excel | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions | |||
how to delete/clean out the row list in pivot table | Excel Worksheet Functions |