View Full Version : How to set column width on excel

Squall Leonhart
12-04-2003, 01:46 AM
Hi, guys~:)

It's excel time again! (Lu~lu lala~)
Please take a look at following code

'Defines the first row
i = 3
set cells.ColumnWidth(i) = 200
'Creates the column description
objSheet.Range("A" & i).Value = "Code"
objSheet.Range("B" & i).Value = "Description"
objSheet.Range("C" & i).Value = "Price"
objSheet.Range("A" & i & ":C" & i).Font.Bold = True
i = i + 1

'Fills columns for each recordset
While not rs.EOF
objSheet.Range("A" & i).Value = rs("ID")
objSheet.Range("B" & i).Value = rs("Category")
objSheet.Range("C" & i).Value = rs("Description")
i = i + 1

'release sheet
Set objSheet = Nothing

This code is working. But I wanted set specific column(like C) in specific size.
So I wrote the several code like these.

set objSheet = objxls.ActiveSheet
objSheet.cells(C, 0).width =100
objSheet.Range("C" & i).Width = 400

But none of them is working. Do you know any solutions?

12-04-2003, 07:29 AM
have you even tried to find it in excel vba-help??

objSheet.Columns("C").ColumnWidth = 21

Roy Sinclair
12-04-2003, 05:29 PM
Many of the questions like this you can answer for yourself with a little bit of work. One of the things you can do in Excel is "Record a macro", so when you want to find out how to script some common action like resizing a cell width you just need to start recording a macro, perform the resize (or other action) manually, stop the recording and then examine the code in the macro to see how it was done.

It's a great way to find out how to accomplish tasks, I know because I used it several times recently while writing code in VB to automate the creation of a Word document.

Squall Leonhart
12-04-2003, 07:30 PM
Thanks, that helped me.
I looked for the site for 3hours.
None of the sites had that code.
That's why I posted.

I have another question.

How can I write the text spanning whole rows.
For example,
| A | B | C | -> Column
This is the title! ->Row 1

So I wrote the code

objSheet.WriteLine("1").value = "This is the title!"

This doesn't work. Do you know solutions?

12-05-2003, 06:51 AM
try Roy's approach:
- start recording a macro in excel
- write some text in A1
- select A1:C1
- press ctrl 1 (format cells)
- select the tab with the option "merge cells"
- check the option
- stop recording the macro
- press alt f11 (VBA-editor)
- examine the macro (i think it is in thisworkbook)
- copy the relevant code to your page