(Excel) Excel Column Width

How to Autofit Excel column Width ?
can anyone please help
Thanks in advance!

1 Like

Hey,

You could use VBScript to achieve that.

For example:

Folder.GetSpecialFolder SpecialFolder:Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> Desktop
set excelFile to Desktop+'\New Microsoft Excel Worksheet.xlsx'

Excel.LaunchAndOpen Path:excelFile  Visible:True Password:'' ReadOnly:False LoadAddInsAndMacros:False Instance=> ExcelInstance

System.RunVBScript VBScriptCode:'''
Set objExcel = GetObject("'''+excelFile+'''").Application
objExcel.Application.Visible = True

Set objSheet = objExcel.Sheets("Sheet1")

for col=1 to 3
	objSheet.columns(col).AutoFit()
next''' \
                   ScriptError=> ScriptError \
                   ScriptOutput=> ScriptOutput

You’ll probably need to adjust the name of the Sheet and the range of Columns, in the VBScript code to fit your excel file.

2 Likes

Hi @abhi1100, as an alternative to @kzaverdinos’s method, you can do it with an appmask and some UI automation.
imf3

This is just the appmask (trouble uploading image files), but here’s some code. On my system, the entire sheet is selected when Excel opens. I click the ‘Format’ menu item using the appmask, and then send ‘i’.

    import "C:\IMF.appmask"

    Excel.LaunchAndOpen \
        Path: "C:\IMF.xlsx" \
        Visible:True Password:'' \
        ReadOnly:False \
        LoadAddInsAndMacros:False \
        Instance=> ExcelInstance
        
    UIAutomation.Click \
        Element: IMF.IMF.IMF_Excel.mnuFormat \
        ClickType:UIAutomation.ClickType.LeftClick \
        MousePositionRelativeToElement:UIAutomation.RectangleEdgePoint.MiddleCenter \
        OffsetX:0 OffsetY:0
        
    wait 1

    MouseAndKeyboard.SendKeys TextToSend: 'i' DelayBetweenKeystrokes:10

Regards,
burque505

3 Likes