Setting a filter in Excel Pivot

asmilie2b

Hello all,

I am making my first pivot in an excel that I have data exporting to from a report in Nav.
It's coming along nicely but the final thing I need to do is to have the pivot filtered on a certain value.

I am able to set the field in excel that I want to have the filter on as such:

xlPivotField := xlPivotTable.PivotFields('Country Code');
xlPivotField.Orientation := 3; //Filter Field

Then I wish when the excel opens to have the Country Code filter set to the value "JP", but I am unsure of the automation subclass and syntax to use. I have tried:

xlPivotField.CurrentPageName('JP');
xlPivotField.CurrentPage := 'JP';

But I am either getting the syntax wrong or these are not the right ones as I receive error.s
Also there is I can see xlPivotField.PivotFilters in the C/AL symbol menu but it takes no parameters.

Any help would be much appreciated.

BernardJ

xlPivotField.CurrentPage('JP'); should do the job, but be aware that the item 'JP' actually needs to exist in the list of country codes you exported to Excel.

If you are not sure about the syntax to use, record a macro in Excel and view the VB code afterwards.

Regards

asmilie2b

Hi Bernard,

Many thanks for the response. That worked.
It was right in front of me. I do check the script in the macro editor, and that tells me the parameters/automation servers I should be using, but I'm not quite there with converting that into C/AL.