Wordart displays inncorrectly when created using VB.Net (2008)   

I do a reasonable amount of creating Excel sheets using VB.Net. For the 
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

date: Fri, 11 Apr 2008 05:19:01 -0700   author:   Tonylad