vb.net late bindings issue even after setting the variable

Question

Hey all I have the following code thats throwing Late bindings.

I thought putting:

Dim excelWS As Worksheet

excelWS = New Worksheet

Before using the variable corrected the late binding issue?

UPDATE 1

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

UPDATE 2

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)).

its vb.net . why are you not referencing Imports'Microsoft.Office.Interop.Excel and using 'CreateObject' ? — Jonathan Applebaum, Aug 29 '16 at 17:24
no problem, is it possible to add your code as a text and not as an image? — Jonathan Applebaum, Aug 29 '16 at 17:41

Jonathan Applebaum · Accepted Answer · 2016-08-29T18:10:40.090

0

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"


        xlws.Move(After:=xlwb.Sheets(xlwb.Sheets.Count))
        ' note: .value is a Range property
        xlws.Cells(1, 2) = "standard"


        xlwb.Application.DisplayAlerts = False
        exapp.Visible = True
        xlwb.Worksheets("sheet1").Delete()
        xlwb.SaveAs("C:\Users\john\Desktop\test.xlsx")
        xlwb.Close()

    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

edited Aug 29 '16 at 18:10

answered Aug 29 '16 at 17:54

Jonathan Applebaum

5,738
4
33
52

Did you mean to put **Excel.** with all of those? Can you not use just **Application or Workbooks or Worksheets**? – StealthRT Aug 29 '16 at 18:02
yes you can but change Imports Microsoft.Office.Interop to Imports Microsoft.Office.Interop.Excel – Jonathan Applebaum Aug 29 '16 at 18:07
Check out my OP for the 2nd update to see what I get now when changing everything as you suggested. – StealthRT Aug 29 '16 at 18:10
i think you are tying to move worksheet that has index of 0 (the most left /last worksheet) and it is not possible to move it to index -1. am i right? – Jonathan Applebaum Aug 29 '16 at 18:17
It's in a loop and will be creating a few tabs. I set all the excel calls to nothing and i thought having that would make a new one for it? – StealthRT Aug 29 '16 at 18:26
no, to add new worksheet use xlws = xlwb.Worksheets.Add() xlws.Name = "MY WS" . have a look in the code i have added. – Jonathan Applebaum Aug 29 '16 at 18:33
It's now telling me that **excelWS.Name** is **'excelWS' is used before it has been assigned a value. A null reference exception could result at runtime.** – StealthRT Aug 29 '16 at 18:53
I got it finally. Thanks for the help, Jonathan. – StealthRT Aug 29 '16 at 19:59

vb.net late bindings issue even after setting the variable

1 Answers1