Excel object: How to Format->Column->AutoFit Selection

[mkrausnick]

After creating a one-sheet XLS workbook and before emailing it to the user, I want to automate the equivalent of:

Select All
Format->Column->AutoFit Selection

using VFP9.
What syntax would accomplish this?


Thanks,

Mike Krausnick
Dublin, California

 

[robsuttonjr]

Copy the following to a file called 'excelautofit.prg'. You would then call it from a command:
DO excelautofit.prg with 'c:\myfile.xls'


LPARAMETERS lcFileName

*PUBLIC oExcel, oWorkBook
oExcel = CREATEOBJECT("Excel.Application")
oWorkBook=oExcel.Workbooks.Open(lcFileName)
oExcel.Columns.AutoFit()
oWorkbook.Save()
oWorkbook.Close()

RELEASE oWorkbook
RELEASE oExcel


Regards,

Rob

 

[baltman]

Since Excel isn't the brighted sharpest tool in the shed, to get an optically pleasent result you might need to do a little more. I take the initial witdh that excel decides on, set the column to to slightly larger and then autofit again.

This is a bit out of context, but it might help.

.Rows("4:4").RowHeight = 200

.Columns("A:D").EntireColumn.ColumnWidth = 2
.Columns("A:D").EntireColumn.AutoFit

FOR XLColNum = 5  TO lnColCount &&figure out each column width for best header looks
    lcColumn=IIF(XLColNum<=26,CHR(64+XLColNum),CHR(64+INT((XLColNum-1)/26))+;
		IIF(XLColNum>26,CHR((64+(XLColNum-(INT((XLColNum-1)/26)*26)))),""))

   		lcColumnandRow=lcColumn+"3"		        
		
        lnColumnWidth=IIF(VARTYPE(.Range(lcColumnandRow).value)=[X],0,.Range(lcColumnandRow).value)+2 &&value made in VFP
        .Range(lcColumnandRow).Value=""
        .Columns(lcColumn).EntireColumn.ColumnWidth = MAX(2,lnColumnWidth)
        .Columns(lcColumn).EntireColumn.AutoFit
 ENDFOR
 
.Rows("4:4").EntireRow.AutoFit

 

[mkrausnick]

Rob,
Thanks - that worked. I didn't realize you didn't have to select the columns. Always something new to learn.

baltman,
Thanks for the reply. Interesting code. I'll study it.

Mike Krausnick
Dublin, California