Hi all,
first step I have prepared a file -template.xls- with a macro inside
This macro will run at open time and it will build a pivot table.

This is the code:
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.AddDataFiel
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("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


Then I have written a java file that reads as input a txt file and th
template.xls and produces the final PIPPO.XLS that contains the data o
the txt file and the macro of the TEMPLATE.TXT so that when th
PIPPO.XLS is opened the PIVOT table will be built.
Now the problem is related to the macro code because if I want to ad
inside it other code such as for ex.
Range("AC2").Select
ActiveCell.FormulaR1C1 = _

"=OR(NOT(OR(RC[-27]=""CLOSED"",RC[-27]=""RETURNED"")),AND(OR(RC[-27]=""CLOSED"",RC[-27]=""RETURNED""),NOT(OR(RC[-16]=""Use
Error"",RC[-16]=""Invalid"",RC[-16]=""Duplicate"",RC[-16]=""Mis
Invalid"",RC[-16]=""Unreproduced"",RC[-16]=""Withdrawn"",RC[-16]=""Reques
Data""))))"
Range("AC2").Select
Selection.Copy
' Range("AC3:AC13").Select
Range("AC3:COUNTA(Defects!C1").Select
ActiveSheet.Paste


when I try to open the PIPPO.XLS it hangs.
Before post this thread I try in any posible way with no results.
Can anybody help me?
Thanks a lot in advance for any suggestion.

Regards
scard

--
scard
-----------------------------------------------------------------------
scardy's Profile: http://www.excelforum.com/member.php...fo&userid=3358
View this thread: http://www.excelforum.com/showthread.php?threadid=53373