Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question How to set column width on excel

    Hi, guys~

    It's excel time again! (Lu~lu lala~)
    Please take a look at following code
    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")
    	rs.MoveNext
    	i = i + 1
    Wend
    
    '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.
    Code:
    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?
    Thanks

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    have you even tried to find it in excel vba-help??

    objSheet.Columns("C").ColumnWidth = 21
    I am the luckiest man in the world

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Check out the Forum Search. It's the short path to getting great results from this forum.

  • #4
    New Coder
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Another question

    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


    Code:
    objSheet.WriteLine("1").value = "This is the title!"
    This doesn't work. Do you know solutions?

  • #5
    Senior Coder
    Join Date
    Jun 2002
    Location
    Zwolle, The Netherlands
    Posts
    1,120
    Thanks
    2
    Thanked 31 Times in 31 Posts
    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
    I am the luckiest man in the world


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •