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.

Could you submit a bug report to the XlxsWriter GitHub with a file containing one Osisoft function. I will have a look at it. It isn’t always possible to support third party functions but sometimes it is. — jmcnamara, Sep 13 '21 at 07:52

score 0 · Answer 1 · answered Sep 13 '21 at 02:58

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

xlsxwriter click on cells or apply function

1 Answers1