VBScript: freeze Excel rows

[Guillaume]

Hello,

I have a VB script in EA that runs a custom Excel export of classes and attributes.
I'd like to freeze the first 3 rows (headers + subheaders) on the worksheet.

I found some information in VBA about selecting rows and running FreezePanes = true, yet I can't get it to work in EA.
There is an old related post on this forum but it involves a C# code (not scripting): https://sparxsystems.com/forums/smf/index.php/topic,9005.0.html

Has anyone found a way to achieve this ?

[Geert Bellekens]

Yeah, there's something weird with that function since Office 2016. It doesn't work anymore if you don't have your window visible

This is how I solved it:

See https://github.com/GeertBellekens/Enterprise-Architect-VBScript-Library/blob/master/Framework/Utils/ExcelFile.vbs

Code: [Select]

public function freezePanes(ws, row, column)
'select the worksheet
ws.Activate
m_ExcelApp.ActiveWindow.WindowState = xlMaximized
m_ExcelApp.ActiveWindow.SplitRow = row
m_ExcelApp.ActiveWindow.SplitColumn = column
m_ExcelApp.ActiveWindow.FreezePanes = true
end function
Geert

[Guillaume]

Hi Geert,

Thank you for the information, that's exactly what I was after