ACCES TO PIVOT TABLE

[#1]

Hello,

Please how can we access the cell of row i and column j of a pivot table programmatically?

Thank you

Écouter

Lire phonétiquement

[#2]

I pose the problem clearly.

I developed an application that has multiple sheets QVW.

Each sheet has at least a PivotTable.

I'd like to retrieve a value from a cell in a PivotTable that is specified in a sheet, and store this value in another cell in a PivotTable that is in another sheet.

Please help me it'is very urgent.

Thanks

[suniljain]

" I'd like to retrieve a value from a cell in a PivotTable that is specified in a sheet, and store this value in another cell in a PivotTable that is in another sheet."

Answer:

I think it is not possible. Because we cannot store that cell value in pivot table in another sheet.
.

[#4]

I need this value to a calculation

[#5]

Hi Yimen,

I am quite sure, its impossible to adress a cell. You can't adress that doesn't exist. All the pivots and straight table are very dynamic and only calculated when needed. Thats one of the main advantiges of QV. Think about the usr is changing any selection --> your table looks different than before. How to follow any x/y-adress in this enviroment ?

Regards and nice weekend

Roland

[#6]

OK

Here in fact the problem.

I have a table in my application. The table name is: Bilan_Actif_Exercice_NM1

The statement that creates the table is as follows:

Bilan_Actif_Exercice_NM1:
LOAD AS CodeLigne CL_Actif,
Amount AS MT_Actif_NM1
FROM
Parameter_Files \ Bilan_Actif_Exercice_NM1.xls
(OOXML, embedded labels, table is Exercice_NM1);

I want to get a value in this table.

Applying the following statement:

SET BHn = IF (CL_Actif = 'BH', MT_Actif_NM1);

When I reload the data, the variable BHn does not contain values. I do not know why?

I checked that the table contains the data.

Please help me .

[#7]

Hi Yimen

Maybe following example will help you:

set table = ActiveDocument.GetSheetObject( "MYTABLE" )
totalRows = table.GetRowCount
for RowIter = 1 to totalRows-1
for ColIter =0 to table.GetColumnCount-1
set cell = table.GetCell(RowIter,ColIter)
cellText = cell.Text
...
..
next
next

[#8]

Hi Ruslan, please i don't understand this statement.

Could you explain me this clearly.

Thanks

[#9]

Ok,

i think you should store it in a new field "BHn" into your table Bilan_Actif_Exercice_NM1.

Try this:

LOAD AS CodeLigne CL_Actif,
Amount AS MT_Actif_NM1,

IF (CodeLigne = 'BH', MT_Actif_NM1, else? ) AS BHn
FROM .....

Regards

Roland

[#10]

This is piece of Macros code written on VB which I am using to scan pivot table to automate some stuff in my QV

With function GetCell(RowIter,ColIter) you can get any cell from your pivot table