This statement does not work in VB

[Tod]

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

 

[Tom Ogilvy]

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

 

[Tod]

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

 

[Tom Ogilvy]

The next thing I would try would be

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

 

[Tod]

I think I figured it out... sort of. It seems that the
bunch of code does not know what sheet objects to read
from and print to. So it tries working with the
activesheet. So with this code:

objWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:=DataSheet.Range
("A1").CurrentRegion.Address).CreatePivotTable
TableDestination:=ReportSheet.Range("A9")

If the DataSheet is active when it's trying to get the
SourceData, it will get the data with no error. Then if
the ReportSheet is active when it creates the pivottable,
that works okay. So I think I need to divide up this
block of code and put in some worksheet.select
statements.

Does my layman explaination sound plausable?

tod

 

[Tom Ogilvy]

My suggestion reflected the same assessment. You can ignore it if you wish
to go with the recorder code solution.