Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Note. Because of some routine maintenance being done on TechNet (which temporarily prevents us from posting new content), the next Office Space column will appear on Friday, March 25, 2005 instead of Thursday, March 24th.

Wrapping Text in an Excel Spreadsheet

If you’ve ever worked with Excel you’ve undoubtedly encountered the dreaded truncated column effect. In the sample spreadsheet shown below, cell B1 actually contains the phrase This is the text that we want to wrap in column B. The text display is truncated, however, because the column isn’t wide enough to show the entire value:

To overcome this problem and display the full contents of cell B1, you could make the column wider. That works, but if the amount of text in cell B1 is too great you might end up with a spreadsheet that only shows column B. Therefore, an alternative approach is to make the text in the cell wrap; with text wrapping enabled your spreadsheet will look like this:

Cool, huh? But can you enable text wrapping from within a script?

You bet you can. Here’s a script that produces the spreadsheet shown in the preceding graphic:

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B1").EntireColumn
objRange.WrapText = TRUE

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "This is the text that we want to wrap in column B."
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"

The script begins by creating an instance of the Excel.Application object and setting the Visible property to True (that way we can actually see what’s going on). The script then adds a new workbook and a new worksheet.

The part we’re interested in comes next:

Set objRange = objExcel.Range("B1").EntireColumn
objRange.WrapText = TRUE

What we’re doing here is creating an instance of the Range object; we do that by selecting cell B1 and then using the EntireColumn property to extend the range to encompass all of column B.

At that point we set the WrapText property to True. Because our range consists of every cell in column B, we’ve now enabled word wrapping for each and every cell in column B. The last four lines of the script simply enter some text into cells A1 through A4, just so we can see the effects of the word wrapping.

One thing you might also want to do when you enable text wrapping is change the vertical alignment of all the cells. By default, text is aligned on the bottom of the cell. To align text to the top of the cell, just set the value of the VerticalAlignment property to -4160. (Yes, we know: that is a weird value.) Here’s a script that uses the UsedRange property to select the portion of the worksheet that actually contains data. With the used portion of the worksheet selected we then set the vertical alignment of all the cells in the range to the top of the cell:

Const xlVAlignTop = -4160

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

Set objRange = objExcel.Range("B1").EntireColumn
objRange.WrapText = TRUE

objExcel.Cells(1, 1).Value = "A"
objExcel.Cells(1, 2).Value = "This is the text that we want to wrap in column B."
objExcel.Cells(1, 3).Value = "C"
objExcel.Cells(1, 4).Value = "D"

Set objRange2 = objWorksheet.UsedRange
objRange2.VerticalAlignment = xlVAlignTop

Here’s what our revamped spreadsheet looks like: