This statement does not work in VB
 

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

This statement does not work in VB
 

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

This statement does not work in VB
 

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


.

This statement does not work in VB
 

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


.

This statement does not work in VB
 

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

-----Original Message-----
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


.



.

This statement does not work in VB
 

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

--
Regards,
Tom Ogilvy


"Tod" wrote in message
...
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

-----Original Message-----
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


.



.