-
May 9th, 2012, 02:37 PM
#1
Thread Starter
Frenzied Member
[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.
-
May 9th, 2012, 02:52 PM
#2
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.
-
May 9th, 2012, 03:02 PM
#3
Thread Starter
Frenzied Member
Re: Set columns to Word Wrap in Excel from VB
Originally Posted by MattP
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|