The call to member PivotTables failed

Emerik

Hi,
I am having some trouble with a Pivot table. I have a worksheet with data called 'Datagrundlag'. Then I want to create another worksheet with some general header information + a pivot table.
I want to place my pivottable in field A11.

Unfortunately, I run into this error:
The call to member PivotTables failed. Microsoft Excel returned the following message: Metoden PivotTables for klassen Worsheet mislykkedes (which is Danish for: The method PivotTables for class Worksheet failed).

This is my code:

gXLWorkSheet := gXLWorkbook.Worksheets.Add();
gXLWorkSheet.Name := gT218;
gXLWorkSheet.Activate;

gXLWorkSheet.Range('A1').Value := FORMAT(UPPERCASE(gT218));
gXLWorkSheet.Range('A1').Font.Bold := TRUE;

gXLWorkSheet.Range('B3').Value := FORMAT(DebAdr[1]);
gXLWorkSheet.Range('B4').Value := FORMAT(DebAdr[2]);
gXLWorkSheet.Range('B5').Value := FORMAT(DebAdr[3]);
gXLWorkSheet.Range('B6').Value := FORMAT(DebAdr[4]);
gXLWorkSheet.Range('B7').Value := FORMAT(DebAdr[5]);
gXLWorkSheet.Range('A1').Font.Bold := TRUE;

DataRange := 'Datagrundlag!A1:I' + FORMAT(pNoOfRows + 1);
gXLPivotCache := gXLApp.ActiveWorkbook.PivotCaches.Add(1,DataRange);

gXLPivotCache.CreatePivotTable('','PivotTable1');
gXLPivotTable := gXLWorkSheet.PivotTables('PivotTable1');

gXLPivotTable.Location(gT218+'!A11');
 
// Pivot data 
...

The error occurs just around the following code:

gXLPivotTable := gXLWorkSheet.PivotTables('PivotTable1');
gXLPivotTable.Location(gT218+'!A11');

Can anyone tell me why I get this error, and what I should do to avoid it?


UPDATE:

Somehow, this code does not generate any errors:

DataRange := 'Datagrundlag!A1:I' + FORMAT(pNoOfRows + 1);
gXLPivotCache := gXLApp.ActiveWorkbook.PivotCaches.Add(1,DataRange);
gXLPivotCache.CreatePivotTable('','PivotTable1');
gXLWorkSheet := gXLApp.ActiveSheet;
gXLPivotTable := gXLWorkSheet.PivotTables('PivotTable1');
gXLWorkSheet.Name := gT218;

gXLRange := gXLWorkSheet.Range('A1');
gXLRange.FormulaR1C1 := UPPERCASE(gT218);
gXLRange.Font.Bold := TRUE;

but I get errors, if I try to add more header information (for cells B3, B4, B5, etc...).

So I still need to find a way to be able to add header information (and more than 1 cell) AND add a Pivot table in the same sheet.

Best regards,
Emerik