StealthRT StealthRT - 1 year ago 55 Question late bindings issue even after setting the variable

Hey all I have the following code thats throwing Late bindings.
enter image description here

I thought putting:

Dim excelWS As Worksheet

excelWS = New Worksheet

Before using the variable corrected the late binding issue?


Would it be something like this?

Dim excelRange As Range
Dim excelApp As Application
Dim excelWB As Workbook
Dim excelWS As Worksheets

excelWB = New Workbook

If madeSheet = False Then
excelApp = New Application
excelWB = excelApp.Workbooks.Add
excelApp.Visible = True
End If

excelWS = New Worksheet


On this line now:

excelWS = excelWB.Worksheets.Add(After:=excelWB.Worksheets(sheetLoops))

I am getting the error of:

Additional information: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Worksheets'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208B1-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).

Answer Source

note that you have to referance Microsoft.Office.Interop.Excel assembly: project>>add reference>> check Microsoft Excel x.xx Object Libary

Imports Microsoft.Office.Interop
Public Class Form1
    Private exapp As Excel.Application
    Private xlwb As Excel.Workbook
    Private xlws As Excel.Worksheet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        exapp = New Excel.Application

        xlwb = exapp.Workbooks.Add()
        xlws = xlwb.Worksheets.Add()
        xlws.Name = "MY WS"

        ' note: .value is a Range property
        xlws.Cells(1, 2) = "standard"

        xlwb.Application.DisplayAlerts = False
        exapp.Visible = True

    End Sub
End Class

as for your update:

even that Excel.Worksheet and Excel.Workbook are not static objects, in your case you dont need the make an instant of the (using new) because you are initializing them with new workbook and new worksheet.

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim excelRange As Excel.Range
        Dim excelApp As Excel.Application
        Dim excelWB As Excel.Workbook
        Dim excelWS As Excel.Worksheets

        ' excelWB = New Workbook - you dont need an instant

        ' If madeSheet = False Then
        excelApp = New Excel.Application

        excelWB = excelApp.Workbooks.Add
            excelApp.Visible = True
        '  End If

        ' excelWS = New Worksheet - you dont need an instant
    End Sub