Visual basic with excel automation

Question

I have been unsuccessful in setting the row height and column widths of an excel spreadsheet from within a visual basic application.

I have a visual basic application where I have data in my clipboard. I copy that code to an instance of excel and then have excel save the resulting spreadsheet and then excel closes. I am trying to programmatically set the row heights and cell widths prior to saving the spreadsheet but have been unable to do so. This is the code that I am executing:

If SaveFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    Dim oXL As Excel.Application
    Dim oWB As Excel.Workbook
    Dim oSheet As Excel.Worksheet
    oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    oWB = oXL.Workbooks.Add
    oSheet = oWB.ActiveSheet

    oSheet.Paste()
    oSheet.Cells.Select()
    oSheet.Selection.RowHeight = 11.4
    oSheet.Cells.EntireColumn.AutoFit()
    oSheet = Nothing
    oWB.Close(True, SaveFileDialog1.FileName)
    oWB = Nothing
    oXL.Quit()
    oXL = Nothing
    MsgBox("Finished!")
End If

The application runs without the oSheet.Cells.Select(), oSheet.Selection.RowHeight = 11.4, and oSheet.Cells.EntireColumn.AutoFit() lines. With these lines, I get this error dialog message:

Public member 'Selection' on type 'Worksheet' not found.

When I trace the program in Visual Studio, the oSheet.Paste() command executes and the oSheet.Cells.Select() command executes. The exception is generated when I try to execute the oSheet.Selection.RowHeight = 11.4 command.

Any assistance would be greatly appreciated.

Jonathan

Answer 1

The following is not late binding, it's early binding but the pattern should work with minor code changes.

Option Strict On
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Imports System.IO

Public Class Operations
    Public HasError As Boolean
    Public ErrorMessage As String
    ''' <summary>
    ''' 
    ''' </summary>
    ''' <param name="FileName">Path and file name for Excel file</param>
    ''' <param name="SheetName">Sheet name to work on</param>
    ''' <param name="RowHeight">Used to set row height in SheetName</param>
    ''' <returns></returns>
    Public Function SetWidthHeight(
        ByVal FileName As String,
        ByVal SheetName As String,
        ByVal RowHeight As Integer) As Boolean

        If File.Exists(FileName) Then

            Dim Proceed As Boolean = False

            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
            Dim xlCells As Excel.Range = Nothing

            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False


            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)

            xlApp.Visible = False

            xlWorkSheets = xlWorkBook.Sheets

            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)

                If xlWorkSheet.Name = SheetName Then
                    Proceed = True
                    Exit For
                End If

                Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing

            Next
            If Proceed Then

                xlCells = xlWorkSheet.Cells
                xlCells.PasteSpecial()

                Dim EntireRow As Excel.Range = xlCells.EntireRow
                '
                ' Set row height 
                '
                EntireRow.RowHeight = RowHeight
                Dim ColRange = xlCells.EntireColumn
                '
                ' Auto fit all columns
                '
                ColRange.AutoFit()
                ReleaseComObject(ColRange)

                ReleaseComObject(xlCells)
                ReleaseComObject(EntireRow)

            Else
                HasError = True
                ErrorMessage = SheetName & " not found."
            End If

            xlWorkSheet.SaveAs(FileName)

            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()

            ReleaseComObject(xlWorkSheets)
            ReleaseComObject(xlWorkSheet)
            ReleaseComObject(xlWorkBook)
            ReleaseComObject(xlWorkBooks)
            ReleaseComObject(xlApp)

        Else
            HasError = True
            ErrorMessage = "'" & FileName &
                "' not located. Try one of the write examples first."
        End If

        Return HasError
    End Function
    Private Sub ReleaseComObject(ByVal excelObject As Object)
        Try
            If excelObject IsNot Nothing Then
                Marshal.ReleaseComObject(excelObject)
                excelObject = Nothing
            End If
        Catch ex As Exception
            excelObject = Nothing
        End Try
    End Sub
End Class