Wordart displays inncorrectly when created using VB.Net (2008)
Tonylad
particular application, I want to add a watermark to the center of every
excel sheet page (except the first) so if they are printed out or sent
electronically, they have the word "Confidential" on the sheets.
I'm using VS2008 and Office 2003.
I've created a little application to show how this works (or doesnt!) which
you can copy and paste into a new application (with one form and one button).
If you use the below code as it is, it will work fine. The only problem is
that I dont want to make the excel object visible until all the code is
finished (because if the user clicks on the excel sheet as it's being
populated it will crash). If you then comment out the visible location 1 line
and uncomment the visible location 2 line, It will crash. If you skip past
the error just to see what it has done, you will see randomly placed huge
confidential watermarks (see page 32).
Any help will be greatly appreciated!
The code is below:
Code Snippet
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class Form1
Dim excelApplication As Excel.Application
Dim excelWorkBook As Excel.Workbook
Dim excelWorkSheet As Excel.Worksheet
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
excelApplication = CreateObject("Excel.Application")
excelWorkBook = excelApplication.Workbooks.Add
excelWorkSheet = excelWorkBook.Worksheets(1)
excelWorkSheet.Range("H160").Value = "Page 4 Data"
excelApplication.Visible = True ' Visible location 1
AddConfidentials(1)
'excelApplication.Visible = True ' Visible location 2
End Sub
' Adds a single "Confidential" watermark to the sheet at the location
specified
Private Sub AddWaterMark(ByVal CurrentRow As Integer, _
ByVal StartAfterColumn As Integer)
Dim Confidential As Excel.Shape =
excelWorkSheet.Shapes.AddTextEffect( _
Microsoft.Office.Core.MsoPresetTextEffect.msoTextEffect2,
"Confidential", _
"Arial Black", 24, Microsoft.Office.Core.MsoTriState.msoFalse, _
Microsoft.Office.Core.MsoTriState.msoTrue, _
ColumnPixelEnd(StartAfterColumn), RowPixelStart(CurrentRow))
With Confidential
.Adjustments(1) = 0
.Fill.Transparency = 0.75
.Line.Visible = Microsoft.Office.Core.MsoTriState.msoFalse
.ScaleWidth(2, Microsoft.Office.Core.MsoTriState.msoFalse)
End With
End Sub
' Adds the confidential watermarks to the center of each page
Private Sub AddConfidentials(ByVal StartAfterColumn As Integer)
' Set the view to pagebreak view
' (we have to do this to find out where the pagebreaks are)
excelApplication.ActiveWindow.View = XlWindowView.xlPageBreakPreview
For intBreakCount As Integer = 2 To excelWorkSheet.HPageBreaks.Count
Dim intPageBreak1 As Integer = _
excelWorkSheet.HPageBreaks(intBreakCount - 1).Location.Row
Dim intPageBreak2 As Integer = _
excelWorkSheet.HPageBreaks(intBreakCount).Location.Row
Dim intHalfWayRow As Integer = ((intPageBreak2 - intPageBreak1)
/ 2) _
+ intPageBreak1
AddWaterMark(intHalfWayRow - 3, StartAfterColumn)
Next
' Add a watermark to the top of the last page
Dim intLastBreak As Integer = excelWorkSheet.HPageBreaks( _
excelWorkSheet.HPageBreaks.Count).Location.Row
AddWaterMark(intLastBreak + 2, StartAfterColumn)
' Set the view back to normal
excelApplication.ActiveWindow.View = XlWindowView.xlNormalView
End Sub
' Gets how many pixels from the first row to the top of the selected row
Private Function RowPixelStart(ByVal intRow As Integer) As Decimal
Dim decPixelCount As Decimal = 0
For intRowCounter As Integer = 1 To intRow - 1
decPixelCount += excelWorkSheet.Rows.Item(intRowCounter).height
Next
Return decPixelCount
End Function
' Gets how many pixels from the first column to the end of the selected
column
Private Function ColumnPixelEnd(ByVal intColumn As Integer) As Decimal
Dim decPixelCount As Decimal = 0
For intColCounter As Integer = 1 To intColumn
decPixelCount += excelWorkSheet.Columns.Item(intColCounter).width
Next
Return decPixelCount
End Function
End Class