Run Macro after Calc has loaded external data

[crabbit]

Hi, I have a Calc sheet that loads from a local text file. All works well, but some of the columns are currency values and I’d like the format to show two decimal places. If I format in advance, this is overwritten when the file loads on opening (which is different from Excel). No worries, I have a macro that applies the desired formatting, but I can’t find the best event to trigger this. I have tried “Document Finished Loading”, “Open Document”, “View Created” but these call get called before the data is loaded. I added a MsgBox at the start of my macro to convince myself that the macro was being called, and that was enough to delay the execution and the formatting applied.

Is there a way of achieving what I desire? And suggestions?

Version: 7.3.7.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 4; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Ubuntu package version: 1:7.3.7-0ubuntu0.22.04.4
Calc: threaded

[sokol92]

@crabbit

I have a Calc sheet that loads from a local text file

Your question is not entirely clear.
Which load technology do you use (there are several)?
If you use macros, you can first load the data with a macro and then format the sheet. There is no need to handle events.
Maybe upload some sample data here?

[crabbit]

Apologies not clear. External local text file is inserted using Sheet, Link to External Data and the contents are updated automatically each time Calc sheet is opened. I want to do some post-load formatting of the columns holding currency values.

I can load a minimal example if needed.

Agreed I can do the load and format in a macro, but would prefer to use built in functionality for the load if possible. Data sets are large, so should likely be more efficient. Thanks.

[sokol92]

Try the following scheme.
In the macro below, replace Msgbox with formatting actions.
Formatting will be performed every time the data is updated (including when the file is opened).
The Onload macro should be assigned to the View Created document event.

Option Explicit
Global oModifyListener As Object

Sub OnLoad()
  SetModifyListener()
End Sub

Sub SetModifyListener()
    oModifyListener = createUnoListener("CellModify_","com.sun.star.util.XModifyListener")
    ThisComponent.Sheets(0).getCellRangeByName("A1").addModifyListener oModifyListener   
End Sub

Sub CellModify_modified(oEvent)
    Wait 1000
    Msgbox "Formatting here..."
End Sub

Sub CellModify_disposing(oEvent)
    StopModifyListener
End Sub
  
Sub StopModifyListener()
  If Not (oModifyListener Is Nothing) Then oModifyListener=Nothing
End Sub

[crabbit]

Perfect, thank you @sokol92!