:o 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
We use the *obj/excel object to export to Excel. With that set in MyExcel, we do 2 steps:
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.
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
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:
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.