How to autofit column widths in Excel?

[angeliquec]

Hi,

This question is related to a question in a previous thread:

I could not get the correct formula for getting the absolute_column_width despite getting each column's width by proc contents.

I'm thinking of an alternative in fixing the column widths by VB script instead. Input and output would be in XLS. Then, the VB script is to be called in SAS with data _null_; file "something.vbs";

However, I'm not knowledgeable in VB scripts. Is this possible?

Thank you!

[RW9]

Hi,

WEll, in VBA (note slightly different to VB script), you can do the following:

Cells.Select

Cells.EntireColumn.Autofit

However I think you should be fixing this when you create the document as you may not end up with what you expect.  How are you creating the XSLX?  When I do this I have proc report and I fix column widths within the proc report, otherwise if you autofit and one record has lots of text, your report will look wrong.  So I suggest doing:

ods tagsets.excelxp file="....xlsx";

proc report style=minimal nowd split="|";

     columns _all_;

     define     col1 / "Column1" style(column)=[width=10cm just=l] style(header)=[just=l];

     ...

run;

[angeliquec]

Hi RW9,

Actually I created a XML file first via ods tagsets.excelxp to output the SAS formats. I use a simple proc print statement within the ODS statements.

After the XML output, I used macro statements (which have VB scripts embedded) to convert the XML file to XLS.

I'd like the code on the setting of the column widths be flexible even if the table structure has changed -- such that I do not need to list all the columns one by one and make manual changes on the input widths of the columns.

[RW9]

Hi,

Well if your doing it in VBA (Visual Basic for Applications - Note VB scripting is something else so don't confuse the two), then just add my two (3 if you need to select sheet) lines of code below:

    Sheets("Sheet1").Select

    Cells.Select

    Cells.EntireColumn.AutoFit

Put that in your VBA sub and it will select Sheet, select all rows, then autofit.

Am still not sure why you need the XLS format, this is an old Office Proprietary format which has been replaced by the Open Office XLSX (which is a Zip file with directories and XML within).  Your also better off using proc report rather than print as you can set styles, widths etc. and thus create actual stylized reports, me personally I wouldn't rely on Excel to do that, all it will take is one long string and your whole report will become messed up.

[angeliquec]

Hi RW9,

Thanks. I have applied AutoFit in VBScript code which I made and executed with SAS.

We had to output XLS tables to maintain what was set in our project.

[RW9]

Sounds like someone should re-assess the project, XLS is both proprietary - I.e. closed sourced sourced compiled, and not even the latest version from M$.  SDLC review what its doing regularly and update where necessary.