The next thing I would try would be

SourceData:=DataSheet.Range _
("A1").CurrentRegion.Address(0,0,-4150 ,True)

--
Regards,
Tom Ogilvy

"Tod" wrote in message
...
Thanx for the reply Tom. I'm declaring my connection and
recordset at the top of the procedure like this:

Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

I tried using 1 in place of xlDatabase and received the
same error.

tod

-----Original Message-----
If you are using late binding, then I would replace
xlDatabase with the
number 1

--
Regards,
Tom Ogilvy

"Tod" wrote in message
...
I don't know if this is more of an Excel VBA issue or a
VB issue, so I've posted this question in both places.

I created some code in a workbook that gets some data
from a database, puts it in a worksheet, then creates a
pivottable from that data. When I run it from within
the
workbook it works fine.

However, now I want to put the code in VB and have VB
get
the data from the database, open the workbook, put the
data in the workbook, then create the pivottable from
that data. The code works except for this part:

'in VB I am passing the workbook to the code as
'objWorkbook. In Excel I just use ThisWorkbook.
'DataSheet is the worksheet object that holds the
'data. Reportsheet is the worksheet object where
'the pivottable goes.
objWorkbook.PivotCaches.Add(SourceType:=xlDatabase ,
SourceData:= _
DataSheet.Range
("A1").CurrentRegion.Address).CreatePivotTable
TableDestination:= _
ReportSheet.Range("A9")

This code works in Excel, but when run from VB it
causes
Excel to give the following error:

The PivotTable field name is not valid. To create a
PivotTable, you must use data that is organized as a
list
with labeled columns. If you are changing the name of
the
PivotTable field, you must type a new name for the
field.

How can I alter my code so that VB can run it against
the
workbook?

tod


.