Automate the macro - optimal columns width on open, every sheet

[JanDawid]

Hi,

In our company, we open files a few times a day that need to be fixed by macro.

The macro is responsible for setting the optimal column width but it sets columns width only on one sheet at a time and need to be triggered by hotkey. Files have 4 sheets always.

My question for help: is it possible to run this macro on every sheet from the file - automatically when opening the files?

I would be so grateful for help, it would save us a lot of time.

We are using this macro:

Option Explicit

Function SetOptimalWidth(Optional oDummy As Variant) As String
Const MAX_WIDTH = 10000 
Dim oCurrentController As Variant
Dim oActiveSheet As Variant
Dim oCursor As Variant
Dim oColumns As Variant
Dim oColumn As Variant
Dim i As Long
Rem First of all, we find out the active sheet
	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
Rem In order not to change all the columns on the sheet, we find the UsedRange 
	oCursor = oActiveSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oColumns = oCursor.getColumns()
Rem Now we optimize the width of each column in a given range
	For i = oColumns.getCount() - 1 To 0 Step -1
		oColumn = oColumns.getByIndex(i)
		oColumn.OptimalWidth = True
Rem We could stop at this
Rem But some columns may become so wide that they won’t fit on the screen
Rem In this example, we will limit the maximum width to 10 cm
		If oColumn.Width > MAX_WIDTH Then
			oColumn.OptimalWidth = False
			oColumn.Width = MAX_WIDTH
		EndIf 
	Next i
	SetOptimalWidth = "Clear this cell if you want to stop the automatic column width"
End Function

[karolus]

Hallo

def opt_width_below_10(*_):
    MAX_WIDTH = 10000
    doc = XSCRIPTCONTEXT.getDocument()
    for sheet in doc.Sheets:
        cursor = sheet.createCursor()
        cursor.gotoEndOfUsedArea(True)
        cursor.Columns.OptimalWidth = True
        for column in cursor.Columns:
            if column.Width > MAX_WIDTH:
                column.OptimalWidth = False
                column.Width = MAX_WIDTH

its python, use it (probably with the help of apso.oxt from there) or translate it by your own!

[JanDawid]

Thank you very much!

Here is the working code for BASIC:

Option Explicit

Sub SetOptimalWidthForAllSheets()
Const MAX_WIDTH As Long = 10000
Dim doc As Object
Dim sheets As Object
Dim sheet As Object
Dim cursor As Object
Dim columns As Object
Dim column As Object
Dim i As Long

' Get the current document
doc = ThisComponent

' Get all sheets in the document
sheets = doc.Sheets

' Loop through each sheet
For i = 0 To sheets.getCount() - 1
    sheet = sheets.getByIndex(i)
    
    ' Create a cursor for the sheet and find the end of the used area
    cursor = sheet.createCursor()
    cursor.gotoEndOfUsedArea(True)
    
    ' Get the columns in the used area and set optimal width
    columns = cursor.getColumns()
    columns.OptimalWidth = True
    
    ' Loop through each column and set width constraints
    Dim colCount As Long
    colCount = columns.getCount()
    Dim j As Long
    For j = 0 To colCount - 1
        column = columns.getByIndex(j)
        If column.Width > MAX_WIDTH Then
            column.OptimalWidth = False
            column.Width = MAX_WIDTH
        End If
    Next j
Next i

End Sub

[karolus]

@JanDawid

For i = 0 To sheets.getCount() - 1

for each sheet in doc.Sheets