xlsxwriter click on cells or apply function

Question

I have a python script that creates an Excel file.

Definition: Osisoft-function is a function inputted into an Excel cell to get data from an Osisoft server (there is installed a PI DataLink addon into Excel).

Definition 2: To apply an Osisoft-function means to click the cell that contains this function and in the showing menu (to the right) click "apply". Having applied this function, the list of time-value measurements fill the rows under this cell.

In this Excel file, there is written a lot of Osisoft-functions. But all of them need to be applied to actually get the data. It is possible to do it manually, but it becomes tedious when there are 100+ such functions.

The function is parsed into cell using command:

worksheet.write_array_formula(first_row, first_col, last_row, last_col, formula[,
  cell_format[, value]])

My question: is it possible to write some kind of macro to click on all these cells and click the apply-buttons in the showing menus? Some kind of selenium-script, but for Excel.

Answer 1

You could write a vba macro like.

Dim Pl As Variant
Dim addIn As COMAddIn
Dim automationObject As Object
Set addIn = Application.COMAddIns("PI DataLink")
Set automationObject = addIn.Object
Worksheets("Sheet with Data").Activate
Worksheets("Sheet with Data").Range("Range ").Select
automationObject.SelectRange
automationObject.ResizeRange