VB.NET Late Binding

Question

For two days, I haven't been able to change the VB-Code to EARLY binding:

Dim Excel02 = New Microsoft.Office.Interop.Excel.Application()
Excel02.Application.WindowState = Excel02.XlWindowState.xlMaximized
Dim Excel02Workbook As Microsoft.Office.Interop.Excel.Workbook
Excel02Workbook = Excel02.Workbooks.Open("C:\Eigene-D\DS-GM\DS-GM-2016.xlsm")
Dim Excel02WorkSheet01 As Microsoft.Office.Interop.Excel.Worksheet
Excel02WorkSheet01 = CType(Excel02Workbook.Sheets("eBay"), Microsoft.Office.Interop.Excel.Worksheet)
Excel02WorkSheet01.Activate()

Excel02.Visible = True

Dim RowEinfuegen1 As Microsoft.Office.Interop.Excel.Worksheet = CType(Excel02WorkSheet01.Rows(10).Resize(5).Insert, Microsoft.Office.Interop.Excel.Worksheet)

The last row is the problem, please help.

What's that last line supposed to do? First off, you should split it into two rows, `Dim RowEinfuegen1 as Microsoft.Office.Interop.Excel.Worksheet`, then `Set RowEinfuegen1 = Ctype(...)`, except I don't know if that `Ctype()` will work, which is why I ask what it's supposed to do. — BruceWayne, Sep 12 '16 at 20:15
@BruceWayne The VB6 `Set` is not a valid keyword in vb.net. The object assignment would simply be `RowEinfuegen1 = CType(..., Microsoft.Office.Interop.Excel.Worksheet)`. It's the `...` which I suspect doesn't make sense. — djv, Sep 12 '16 at 21:05
@Verdolino - Yeah, I suspect it's because he's resizing and inserting, within a method (is that the correct term for what the `Ctype()` is doing?) — BruceWayne, Sep 12 '16 at 21:09

score 0 · Answer 1 · edited May 23 '17 at 12:15

I agree, the last line is a problem. But what are you trying to accomplish?

Dim RowEinfuegen1 As Microsoft.Office.Interop.Excel.Worksheet =
    CType(Excel02WorkSheet01.Rows(10).Resize(5).Insert, 
        Microsoft.Office.Interop.Excel.Worksheet)

The object you're trying to cast

Excel02WorkSheet01.Rows(10).Resize(5).Insert

appears to be a bool

But you are casting it as a Worksheet. A cast is probably not what you want. I wrote some code to create a new sheet in Excel02, and insert the range. Maybe this is what you want to do.

Dim Excel02 = New Microsoft.Office.Interop.Excel.Application()
Excel02.Application.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized
Dim Excel02Workbook As Microsoft.Office.Interop.Excel.Workbook
Excel02Workbook = Excel02.Workbooks.Open("C:\Temp\DS-GM-2016.xlsx")
Dim Excel02WorkSheet01 As Microsoft.Office.Interop.Excel.Worksheet
Excel02WorkSheet01 = CType(Excel02Workbook.Sheets("eBay"), Microsoft.Office.Interop.Excel.Worksheet)
Excel02WorkSheet01.Activate()

Excel02.Visible = True

Dim rowsRange As Microsoft.Office.Interop.Excel.Range = Excel02WorkSheet01.Rows(10)
Dim resizedRange As Microsoft.Office.Interop.Excel.Range = rowsRange.Resize(5)

Dim newBook As Microsoft.Office.Interop.Excel.Workbook = Excel02.Workbooks.Add()
Dim newSheet As Microsoft.Office.Interop.Excel.Worksheet = newBook.Sheets.Add()
Dim newRange As Microsoft.Office.Interop.Excel.Range = newSheet.Rows(1)
newRange.Insert(0, resizedRange)

Remember, you will need to get rid of the unmanaged objects in memory (C# GC won't (promptly) do this automatically). See how

Marshal.ReleaseComObject(newRange)
Marshal.ReleaseComObject(newSheet)
Marshal.ReleaseComObject(newBook)
Marshal.ReleaseComObject(resizedRange)
Marshal.ReleaseComObject(rowsRange)
Marshal.ReleaseComObject(Excel02WorkSheet01)
Marshal.ReleaseComObject(Excel02Workbook)
Marshal.ReleaseComObject(Excel02)

VB.NET Late Binding

1 Answers1