Set Exel Column Widths

[Tom Anderson]

  I am creating an Excel spreadsheet from a data file. Spreadsheet generation completes successfully.

Is it possible to set the Excel column widths from the default size before adding data to spreadsheet?

Thanks!

Tom

[James Zukowski]

We use the *obj/excel object to export to Excel. With that set in MyExcel, we do 2 steps:

Code Select Expand

Stat=MyExcel'SetRange("A1")
if Stat then Stat=MyExcel'Excel'Columns'AutoFit()This ensures we don't limit the sizing to only part of the spreadsheet, and then have Excel set the optimal fit for each column.

[Tom Anderson]

I tried the following but it didn't work:

03090 LET RANGE$="A1:I1"
03100 LET RETVAL=X_OBJ'SETRANGE(RANGE$)
03110 LET RETVAL=X_OBJ'EXCEL'COLUMNS'AUTOFIT()

What am I doing wrong?

Thanks!

Tom

[James Zukowski]

My apologies...I just re-read your original post and saw that you wanted to set the column widths before loading data. What I provided was to set the widths after loading the data.

That said, we also try to pre-set the widths before loading the data, with something like this:

Code Select Expand

N_Cols=<#Columns>
ColWidth[*]=<Column Widths>
! fnCell$(Col,Row) returns the Cell ID for Excel (eg, (4,6) returns "D6")
! fnColumn$(Col) returns the Column ID for Excel (eg, (30) return "AD")
!
for C=1 to N_Cols
  Stat=MyExcel'SetRange(fnCell$(C,1))
  Stat=MyExcel'SetColumnWidth(ColWidth[C])
  ! Assign MyExcel'Excel'Columns(fnColumn$(N))'NumberFormat$ if appropriate
next C
This provides a preliminary sizing, with the AutoFit applied after all of the data is loaded in, and only referring to cell A1.