Pivot Table

[scardy]

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

 

[Guest]

Scardy,

You will probably want to post your macro code. It will help others help you.

 

[scardy]

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),30)"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=
_
"TotData").CreatePivotTable TableDestination:="",
TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.PivotLayout.PivotTable.AddDataField
ActiveChart.PivotLayout. _
PivotTable.PivotFields("NAME"), "Sum of NAME", xlSum
With ActiveChart.PivotLayout.PivotTable.PivotFields("Sum of NAME")
..Caption = "Count of PTRs"
..Function = xlCount
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("PHASEFOUND")
..Orientation = xlColumnField
..Position = 1
End With
With ActiveChart.PivotLayout.PivotTable.PivotFields("ADDDATE")
..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

 

[Guest]

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]

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