Results 1 to 3 of 3

Thread: [RESOLVED] Set columns to Word Wrap in Excel from VB

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Resolved [RESOLVED] Set columns to Word Wrap in Excel from VB

    I am creating a WorkSheet from code. I need my columns to word wrap and not run over (see red arrow below). Not really sure how to do it. Tried to set it for the range of columns with no luck. Here's the current output:



    Here's where I try to set it:

    Code:
        Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim intCurrentRow As Int32 = 5
    
            ' Create Excel Objects
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
            ' Create cell styles
            CreateStyles(xlWorkSheet)
    
            ' Setup the Page
            xlWorkSheet.PageSetup.LeftMargin = 0.25
            xlWorkSheet.PageSetup.RightMargin = 0.25
            xlWorkSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17
            xlWorkSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
    
            ' Configure the columns
            xlWorkSheet.Columns("A:V").Font.Size = 9
            xlWorkSheet.Columns("A:V").AutoFit()
            xlWorkSheet.Columns("A:V").WrapText = True
    
            xlWorkSheet.Columns("B:B").ColumnWidth = 2
            xlWorkSheet.Columns("C:C").ColumnWidth = 6
            xlWorkSheet.Columns("E:E").ColumnWidth = 22
            xlWorkSheet.Columns("I:I").ColumnWidth = 5
            xlWorkSheet.Columns("K:K").ColumnWidth = 3
            xlWorkSheet.Columns("U:U").ColumnWidth = 6
            xlWorkSheet.Columns("V:V").ColumnWidth = 25
    
            ' Load Data
            LoadDistribution(intCurrentRow, xlWorkSheet)
    
            LoadTransmission(intCurrentRow, xlWorkSheet)
    
            'Save the WorkSheet
            xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            ' release Excel objects
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Sub
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

  2. #2
    Frenzied Member MattP's Avatar
    Join Date
    Dec 2008
    Location
    WY
    Posts
    1,227

    Re: Set columns to Word Wrap in Excel from VB

    My job switched to using Google enterprise so I don't have Office on my machine anymore to do testing.

    I believe you need to make an Excel.Range (Column E in your case) and set the WrapText property for the range to True.
    This pattern in common to all great programmers I know: they're not experts in something as much as experts in becoming experts in something.

    The best programming advice I ever got was to spend my entire career becoming educable. And I suggest you do the same.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Mar 2004
    Location
    Orlando, FL
    Posts
    1,618

    Re: Set columns to Word Wrap in Excel from VB

    Quote Originally Posted by MattP View Post
    My job switched to using Google enterprise so I don't have Office on my machine anymore to do testing.

    I believe you need to make an Excel.Range (Column E in your case) and set the WrapText property for the range to True.
    Thanks. I was doing that, but apparently you have to do it AFTER the text is entered in the cell, not before for some reason.

    This version works:

    Code:
        Protected Sub Button1_Click(sender As Object, e As System.EventArgs) Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim intCurrentRow As Int32 = 5
    
            ' Create Excel Objects
            xlApp = New Excel.ApplicationClass
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
            ' Create cell styles
            CreateStyles(xlWorkSheet)
    
            ' Setup the Page
            xlWorkSheet.PageSetup.LeftMargin = 0.25
            xlWorkSheet.PageSetup.RightMargin = 0.25
            xlWorkSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaper11x17
            xlWorkSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
    
            ' Configure the columns
            xlWorkSheet.Columns("A:V").Font.Size = 9
            xlWorkSheet.Columns("A:V").AutoFit()
            xlWorkSheet.Columns("A:V").WrapText = True
    
            xlWorkSheet.Columns("B:B").ColumnWidth = 2
            xlWorkSheet.Columns("C:C").ColumnWidth = 6
            xlWorkSheet.Columns("E:E").ColumnWidth = 22
            xlWorkSheet.Columns("I:I").ColumnWidth = 5
            xlWorkSheet.Columns("K:K").ColumnWidth = 3
            xlWorkSheet.Columns("U:U").ColumnWidth = 6
            xlWorkSheet.Columns("V:V").ColumnWidth = 25
    
            ' Load Data
            LoadDistribution(intCurrentRow, xlWorkSheet)
    
            LoadTransmission(intCurrentRow, xlWorkSheet)
    
            xlWorkSheet.Columns("A:V").WrapText = True
            xlWorkSheet.Columns("A:V").AutoFit()
    
            'Save the WorkSheet
            xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            ' release Excel objects
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
        End Sub
    Sean

    Some days when I think about the next 30 years or so of my life I am going to spend writing code, I happily contemplate stepping off a curb in front of a fast moving bus.

Posting Permissions

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



Click Here to Expand Forum to Full Width