VB .NET and Excel Late Binding

Hi,

I'm using VB .NET 2003 and exporting to Excel using late binding after finding out that early binding and all the different versions of Excel at our Office just won't play nice together.

I thought I tested the following code and it ran fine, but I'm now getting errors on setting up borders. I can't get past that part of code when debugging so I'm sure I'll get more errors after I find the solution to the border problem.

Can an expert please look over the following code and see if I'm doing the late binding of Excel correctly?

Private Sub ExcelExport()
        Application.DoEvents()
        Me.lblSendData.Visible = True
        Me.Refresh()

        oXLApp = CreateObject("Excel.Application")
        Dim oXLBook As Object
        Dim oXLSheet As Object
        oXLBook = oXLApp.Workbooks.Add()
        oXLSheet = oXLBook.Worksheets(1)

        ExcelOpenQryByNCR = 1

        oXLApp.StatusBar = "Adding Worksheet..."
        Me.lblSendData.Text = "Adding Worksheet..."
        Me.Refresh()
        oXLSheet = oXLApp.Workbooks.Add.Worksheets.Add
        oXLApp.Worksheets("Sheet1").Delete()
        oXLApp.Worksheets("Sheet2").Delete()
        oXLApp.Worksheets("Sheet3").Delete()
        oXLApp.Visible = False


        Dim rows As Integer = Me.dsNCRQryMain.Tables(0).Rows.Count
        Dim columns As Integer = Me.dsNCRQryMain.Tables(0).Columns.Count
        Dim r As Integer
        Dim c As Integer
        Dim DataArray(rows, columns) As Object

        'Set header formats
        oXLApp.StatusBar = "Setting Header Format..."
        Me.lblSendData.Text = "Setting Header Format..."
        Me.Refresh()
        oXLApp.Range("A1:M" & (rows + 6).ToString).Select()
        oXLApp.Selection.NumberFormat = "@"
        oXLApp.Range("A1:M1").MergeCells = True
        oXLApp.Range("A1:M6").HorizontalAlignment = -4130 'oXLApp.XlHAlign.xlHAlignCenter
        oXLApp.Range("A1:M1").Font.Size = 14
        oXLApp.Range("A1:M6").Font.Bold = FontStyle.Bold
        oXLApp.Range("A5:M" & (rows + 6).ToString).Font.Size = 8
        oXLApp.Range("A1:M1").Select()
        oXLApp.ActiveSheet.Pictures.Insert("\\ec-utlx\common\IS\utlx2.jpg").Select()

        'Put header info in Excel first
        oXLApp.StatusBar = "Entering Header Info..."
        Me.lblSendData.Text = "Entering Header Info..."
        Me.Refresh()
        oXLApp.Range("A1").Value = "UTLX NCR Report"
        oXLApp.Range("A6").Value = "LOC"
        oXLApp.Range("B5").Value = "NCR"
        oXLApp.Range("B6").Value = "#"
        oXLApp.Range("C5").Value = "APPR/"
        oXLApp.Range("C6").Value = "LN-YR"
        oXLApp.Range("D5").Value = "TANK"
        oXLApp.Range("D6").Value = "#"
        oXLApp.Range("E6").Value = "STS"
        oXLApp.Range("F6").Value = "DISPO"
        oXLApp.Range("G6").Value = "PART #"
        oXLApp.Range("H6").Value = "PO #"
        oXLApp.Range("I6").Value = "INSP"
        oXLApp.Range("J6").Value = "CLASS"
        oXLApp.Range("K5").Value = "WC"
        oXLApp.Range("K6").Value = "RESP"
        oXLApp.Range("L6").Value = "AREA"
        oXLApp.Range("M6").Value = "INSP DATE"
        oXLSheet.Name = "NCR Report"
        oXLApp.Range("A5:M6").Interior.ColorIndex = 44 'yellow-orange
        oXLApp.Range("A5:M6").Interior.Pattern = 1 'xlSolid

        'Draw borders
        oXLApp.StatusBar = "Setting Up Borders..."
        Me.lblSendData.Text = "Setting Up Borders..."
        Me.Refresh()
        With oXLSheet.Range("A5:M6").Borders(oXLApp.XlBordersIndex.xlEdgeBottom)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLSheet.Range("A5:M6").Borders(oXLApp.XlBordersIndex.xlEdgeLeft)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLSheet.Range("A5:M6").Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLSheet.Range("A5:M6").Borders(oXLApp.XlBordersIndex.xlEdgeTop)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With

        'Vertical borders on inner columns
        With oXLApp.Range("A7:A" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("B7:B" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("C7:C" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("D7:D" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("E7:E" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("F7:F" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("G7:G" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("H7:H" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("I7:I" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("J7:J" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("K7:K" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With
        With oXLApp.Range("L7:L" & (numRows + 6).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeRight)
            .LineStyle = oXLApp.XlLineStyle.xlContinuous
            .Weight = oXLApp.XlBorderWeight.xlThin
        End With

        'Double bottom border at end of dataset
        With oXLApp.Range("A" & (6 + numRows).ToString & ":M" & (6 + numRows).ToString).Borders(oXLApp.XlBordersIndex.xlEdgeBottom)
            .LineStyle = oXLApp.XlLineStyle.xlDouble
            .Weight = oXLApp.XlBorderWeight.xlThick
        End With

        'Put details info in Excel next
        oXLApp.StatusBar = "Exporting Data from NCR..."
        Me.lblSendData.Text = "Exporting Data from NCR..."
        Me.Refresh()
        For c = 0 To columns - 1
            DataArray(r, c) = Me.dsNCRQryMain.Tables(0).Columns.Item(c).ColumnName
            For r = 0 To rows - 1
                DataArray(r, c) = Me.dsNCRQryMain.Tables(0).Rows(r).Item(c)
            Next
        Next
        oXLSheet.Range("A7").Resize(rows, columns).Value = DataArray

        'Format the dataset area
        oXLApp.StatusBar = "Setting Up Data Area..."
        Me.lblSendData.Text = "Setting Up Data Area..."
        Me.Refresh()
        oXLApp.Range("A6").Columns.ColumnWidth = 4 'LOC
        oXLApp.Range("B6").Columns.ColumnWidth = 7 'NCR
        oXLApp.Range("C6").Columns.ColumnWidth = 6 'APPRO #
        oXLApp.Range("D6").Columns.ColumnWidth = 5 'TANK #
        oXLApp.Range("E6").Columns.ColumnWidth = 4 'STATUS
        oXLApp.Range("F6").Columns.ColumnWidth = 15 'DISPOSITION
        oXLApp.Range("G6").Columns.ColumnWidth = 8 'PART #
        oXLApp.Range("H6").Columns.ColumnWidth = 10 'PO #
        oXLApp.Range("I6").Columns.ColumnWidth = 6 'INSPECTOR
        oXLApp.Range("J6").Columns.ColumnWidth = 6 'CLASSIFICATION
        oXLApp.Range("K6").Columns.ColumnWidth = 5 'WC RESPONSIBILITY
        oXLApp.Range("L6").Columns.ColumnWidth = 5 'AREA
        oXLApp.Range("M6").Columns.ColumnWidth = 9 'INSPECTION DATE
        oXLApp.Range("A7:M" & (6 + numRows).ToString).HorizontalAlignment = -4108 'oXLApp.XlHAlign.xlHAlignCenter
        oXLApp.Range("A7:M" & (6 + numRows).ToString).WrapText = True 'Allow for wrap text that goes beyond column width

        'Loop thru and alternate back color
        If numRows > 1000 Then 'Cut down on the time if the rows are too many
            'do nothing
        Else
            oXLApp.StatusBar = "Adding Alternate Row Colors..."
            Me.lblSendData.Text = "Adding Alternate Row Colors..."
            Me.Refresh()
            Dim x As Integer
            For x = 8 To (numRows + 6) Step 2
                oXLApp.Range("A" & (x).ToString & ":M" & (x).ToString).Interior.ColorIndex = 15  'Gray
                oXLApp.Range("A" & (x).ToString & ":M" & (x).ToString).Interior.Pattern = 1  'xlSolid
            Next
        End If

        'Display the total at the bottom
        oXLApp.StatusBar = "Entering Totals and Parameter Values..."
        Me.lblSendData.Text = "Entering Totals and Parameter Values..."
        Me.Refresh()
        oXLApp.Range("A" & (7 + numRows).ToString).Value = "Total Records Returned: " & numRows
        oXLApp.Range("A" & (7 + numRows).ToString).Font.Size = 10
        oXLApp.Range("A" & (7 + numRows).ToString).Font.Bold = FontStyle.Bold

        'Put parameters used on Excel sheet
        oXLApp.Range("A" & (9 + numRows).ToString).Value = "Search Parameters Used for Above Results:"
        oXLApp.Range("A" & (9 + numRows).ToString).Font.Size = 8
        oXLApp.Range("A" & (9 + numRows).ToString).Font.Bold = FontStyle.Bold
        oXLApp.Range("A" & (10 + numRows).ToString).Value = "NCR Group:"
        oXLApp.Range("A" & (11 + numRows).ToString).Value = "Appropriation:"
        oXLApp.Range("A" & (12 + numRows).ToString).Value = "Status:"
        oXLApp.Range("A" & (13 + numRows).ToString).Value = "Location:"
        oXLApp.Range("A" & (14 + numRows).ToString).Value = "Tank #:"
        oXLApp.Range("A" & (15 + numRows).ToString).Value = "Cell:"
        oXLApp.Range("A" & (16 + numRows).ToString).Value = "Area:"
        oXLApp.Range("A" & (17 + numRows).ToString).Value = "W/C Responsibility:"
        oXLApp.Range("A" & (18 + numRows).ToString).Value = "Part #:"
        oXLApp.Range("A" & (19 + numRows).ToString).Value = "Dispositon:"
        oXLApp.Range("A" & (20 + numRows).ToString).Value = "Classification:"
        oXLApp.Range("A" & (21 + numRows).ToString).Value = "PO #:"
        oXLApp.Range("A" & (22 + numRows).ToString).Value = "Date Inspected:"

        oXLApp.Range("D" & (10 + numRows).ToString).Value = Me.cmbNCRGrp.Text
        oXLApp.Range("D" & (11 + numRows).ToString).Value = Me.cmbAppro.Text
        oXLApp.Range("D" & (12 + numRows).ToString).Value = Me.cmbStatus.Text
        oXLApp.Range("D" & (13 + numRows).ToString).Value = Me.cmbSite.Text
        oXLApp.Range("D" & (14 + numRows).ToString).Value = Me.txtTankStart.Text & " thru " & Me.txtTankEnd.Text
        oXLApp.Range("D" & (15 + numRows).ToString).Value = Me.cmbCellStart.Text & " thru " & Me.cmbCellEnd.Text
        oXLApp.Range("D" & (16 + numRows).ToString).Value = Me.cmbAreaStart.Text & " thru " & Me.cmbAreaEnd.Text
        oXLApp.Range("D" & (17 + numRows).ToString).Value = Me.cmbWCStart.Text & " thru " & Me.cmbWCEnd.Text
        oXLApp.Range("D" & (18 + numRows).ToString).Value = Me.cmbPart.Text
        oXLApp.Range("D" & (19 + numRows).ToString).Value = Me.cmbDispoStart.Text & " thru " & Me.cmbDispoEnd.Text
        oXLApp.Range("D" & (20 + numRows).ToString).Value = Me.cmbClassStart.Text & " thru " & Me.cmbClassEnd.Text
        oXLApp.Range("D" & (21 + numRows).ToString).Value = Me.cmbPOStart.Text & " thru " & Me.cmbPOEnd.Text
        oXLApp.Range("D" & (22 + numRows).ToString).Value = Me.dtpStartDate.Text & " thru " & Me.dtpEndDate.Text

        'Page Setups
        oXLApp.StatusBar = "Setting Page Printout Format..."
        Me.lblSendData.Text = "Setting Page Printout Format..."
        Me.Refresh()
        oXLSheet.PageSetup.CenterHorizontally = True
        With oXLApp.ActiveSheet.PageSetup
            .printtitlerows = "$1:$6"
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = "Print Date " & System.DateTime.Today
            .CenterFooter = ""
            .RightFooter = "Page &P of &N"
            .LeftMargin = oXLApp.InchesToPoints(0.25)
            .RightMargin = oXLApp.InchesToPoints(0.25)
            .TopMargin = oXLApp.InchesToPoints(0.5)
            .BottomMargin = oXLApp.InchesToPoints(0.5)
            .HeaderMargin = oXLApp.InchesToPoints(0.25)
            .FooterMargin = oXLApp.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .CenterHorizontally = True
            .CenterVertically = False
        End With

        'Lock Cells and Set Password to Edit Cells
        oXLApp.ActiveSheet.Protect("baseball", True, True, True, True, True, True, True, True, True, True, True, True, True, True, True)
        oXLApp.ActiveSheet.enableselection = oXLApp.XlEnableSelection.xlNoSelection
        oXLApp.ActiveWorkbook.Protect("baseball", True, True)

        'Finish up
        oXLApp.StatusBar = "Finishing..."
        Me.lblSendData.Text = "Finishing..."
        Me.Refresh()
        oXLApp.ActiveWindow.DisplayGridlines = False
        oXLApp.Range("A1").Select()

        oXLApp.StatusBar = "Ready"
        Me.lblSendData.Text = "Ready"
        Me.Refresh()
        Me.lblSendData.Visible = False
        Me.lblSendData.Text = "..."
        oXLApp.Visible = True
    End Sub

Thanks!Start Free Trial