Excel Pivot Table Selection via Excel.F90 Sample

[garylscott1]

I've been using the excel.f90 sample for quite some time with little difficulty accessing worksheets and data, but I'm unsure quite how to go about getting a pivot table field enabled.  There is only one pivot table in the worksheet/workbook.  I'm not seeing an obvious API to address the pivot table in question.  I would expect, since there can be multiple pivot tables, that there should be a way to address a specific one and to enumerate the list of pivot tables available.  But if there's only one, I would think it should be fairly easy to show the field list (my attempt fails) and to enable the specific field.  The pivot table already exists as needed.  The only change that needs to be made is to enable one field that isn't enabled by default.  Should be simple, yes?

[garylscott1]

The error I receive for:  $Workbook_SetShowPivotTableFieldList($object,$arg1,$status)

is

#define DISP_E_MEMBERNOTFOUND            _HRESULT_TYPEDEF_(0x80020003L)

Within this procedure is contained:

call autoAddArg(invokeargs,'ShowPivotTableFieldList',$arg1)

I've read that MS renamed this field from Excel 2013.  Could that be an issue or is this a generic text reference that resolves to something unambiguous?  I'm unable to find code that actually translates this string to something internally represented to Excel (not that I expect it to be different than this string).

[garylscott1]

That one solved.  I'm still not finding an API to toggle a checkbox.  I'm unsure what exactly this one does, perhaps just returns a pointer to a list of check box items?  Documentation of these is horrible...barely usable.  Where are the memIDs defined and those strings being passed...lordy.

$Worksheet_CheckBoxes($Object,Index,$STATUS)

[Steve_Lionel]

I've never had much luck finding documentation on the COM APIs for Microsoft tools. The Module Wizard just enumerates all of the methods exported from the DLL.

[garylscott1]

What is the correct variant type for index and what does index represent for this procedure?  I've researched literally all day and nothing but wild goose chases :(.  I sort of think I should be able to get a pointer to the checkbox in question here and then use autosetproperty to change the state...yes/no?

INTEGER(INT_PTR_KIND()) FUNCTION $Worksheet_CheckBoxes($OBJECT, Index, $STATUS)

IMPLICIT NONE

INTEGER(INT_PTR_KIND()), INTENT(IN) :: $OBJECT ! Object Pointer

!DEC$ ATTRIBUTES VALUE :: $OBJECT

TYPE (VARIANT), INTENT(IN), OPTIONAL :: Index

!DEC$ ATTRIBUTES REFERENCE :: Index

INTEGER(4), INTENT(OUT), OPTIONAL :: $STATUS ! Method status

!DEC$ ATTRIBUTES REFERENCE :: $STATUS

INTEGER(INT_PTR_KIND()), VOLATILE :: $RETURN

INTEGER(4) $$STATUS

INTEGER(INT_PTR_KIND()) invokeargs

invokeargs = AUTOALLOCATEINVOKEARGS()

CALL AUTOADDARG(invokeargs, '$RETURN', $RETURN, AUTO_ARG_OUT, VT_DISPATCH)

IF (PRESENT(Index)) CALL AUTOADDARG(invokeargs, '$ARG1', Index, AUTO_ARG_IN)

$$STATUS = AUTOINVOKE($OBJECT, 824, invokeargs)

IF (PRESENT($STATUS)) $STATUS = $$STATUS

$Worksheet_CheckBoxes = $RETURN

CALL AUTODEALLOCATEINVOKEARGS (invokeargs)

END FUNCTION $Worksheet_CheckBoxes

[garylscott1]

I see now that the excel.f90 export represents a tiny, inconsistent subset of the available related automation procedures.  It contains some pivot table and checkbox related items but is missing dozens of others.  So I guess one of my mistakes was in assuming that this file was complete or relatively so.  I recommend that this file be replaced with a more fully representative one (especially adding pivot table content) to avoid such confusion in the future and to modify the module wizard to clearly indicate when it is exporting only a subset (including when the originator intentionally selected a subset).  The description in the file does not seem to indicate that it is a subset.  Given the relative importance of Excel and the interaction need, I recommend including a "full" export as well.  It isn't that big, although you probably wouldn't want to use it as-is regularly.  It would save much frustration and confusion in trying to understand how to accomplish these automation tasks.

[Steve_Lionel]

The file in that sample is for use with the sample, and was generated a long time ago. For the sample's purposes there isn't a benefit of regenerating it, with the possibility of introducing inconsistencies. You are free to run the Module Wizard on whatever COM DLL you like.

[garylscott1]

Yes, I did that after wasting nearly 2 days because of lack of clarity as to the completeness of the contents.  This response seriously undervalues the benefit and usefulness of this module to users if it were more complete and with better documentation...alas, it is not unexpected.

[Steve_Lionel]

Documentation would come from Microsoft. I remember spending quite a bit of time trying to puzzle out some issues with this sample. It isn't intended as a "here's all you need for Excel", but rather as an example of what you can do. I never did find a good source for COM interface documentation. I will agree that using the Module Wizard assumes you already know a lot about COM. We had one expert on this, Leo Treggiari, and when Leo left us we (mostly me) struggled with COM issues that came up. Some bugs in the Module Wizard did get fixed over time, but just figuring out what was wrong was a struggle.

Microsoft has moved on from COM and I don't think the return on investment is there to try to beef this up, sad to say. Resources are finite.

[garylscott1]

Frustrating would be a word I would use and it would be a massive understatement :(  Yes I understand.  When I am able to find something relevant on MSDN, it is frequently followed by "for internal use only" and seems to justify why they've included so little information...but even if MS has moved on to the next horribly designed API, the old one will likely hang around for some time to come.  In fact the new APIs seem to have significant hooks into the old as far as I can tell (activex, .NET, mfc yes I know those are old too).

[garylscott1]

Well, this seemingly obvious and simple working example took 3 days of trial and error due to lack of documentation.  I found very little to help in countless web searches as well.  Need more information as to the type of objects being referred to by those pointer return variables, and the expectation of the called procedures for those index variants.  I had thought that the variant definition allowed you some flexibility in setting up the called item, but unfortunately, each called procedure expects a specific variant definition, rather than using the definition as you provide it.  So, if I wanted to specify the item using a 4 byte integer instead of a 2 byte integer, the calls failed with an exception error.  This is just a dumb design.  Far better would be a clearly defined RPC binary call mechanism. 

Part of the research also took me into the checkbox API which appears to be separate from the pivottable checkboxes attached to "fields".  There's probably a way, but it eluded me.  Trial and error is a horrible methodology :(

Anyway tName represents the worksheet (tab) name.  This is notional, I'll now integrate it with the specific report in question and clean up the inconsistencies.  SetShowPivotTabelFieldList isn't needed, but is helpful for debugging.  I added a wait so that I could watch the automation in progress.

call $Application_SetVisible(excelApp,.true.) !Debugging

call $Application_SetDisplayAlerts(excelApp,.true.) !Debuggin

if (tName == 'Report') then

call $Workbook_SetShowPivotTableFieldList(workBook,.true.,status) !Display the field list (testing; requires a cell in the PT to be selected)

call variantInit(vbStrR) !Initialize

vbStrR%VT = VT_bstr

bstrR = convertStringToBSTR("PivotTable1") !Pivot table reference

vBSTRR%VU%PTR_VAL = bstrR

pivotTable = $Worksheet_PivotTables(workSheet,vbstrR,$status=STATUS)

if (status == 0) then

call variantInit(vbStrR) !Initialize

vbStrR%VT = VT_bstr

bstrR = convertStringToBSTR("Resource") !Hard coded field/checkbox name known in this report; otherwise need to query and cycle through list

vBSTRR%VU%PTR_VAL = bstrR

pivotField = PivotTable_PivotFields(pivotTable,vbstrR,$status=STATUS) !Get resource field/checkbox

if (status == 0) then !If we found the resource field, look for any other fields needed, etc

addedField = PivotTable_AddDataField(pivottable,pivotfield,$status=STATUS) !This enables the checkbox

bstrR = convertStringToBSTR("Data") !Data field/checkbox

vBSTRR%VU%PTR_VAL = bstrR

pivotField = PivotTable_PivotFields(pivotTable,vbstrR,$status=STATUS) !Get data field/checkbox

if (status == 0) then

addedField = PivotTable_AddDataField(pivottable,pivotfield,$status=STATUS)

end if

end if

end if

call sleepqq(5000)

end if