CODEWITHSUNDEEP
vb.netexcel-interop
user7234960
user7234960

Reputation:

Get Excel Object when Option Strict On

I have got following codes from this microsoft page.

https://msdn.microsoft.com/en-us/library/e9waz863(v=vs.90).aspx

' Add Option Strict Off to the top of your program. 
Option Strict Off

.

Private Sub getExcel()
Dim fileName As String = "c:\vb\test.xls" 

If Not My.Computer.FileSystem.FileExists(fileName) Then
    MsgBox(fileName & " does not exist")
    Exit Sub 
End If 

' Set the object variable to refer to the file you want to use. 
Dim excelObj As Object = GetObject(fileName)
' Show Excel through its Application property. 
excelObj.Application.Visible = True 
' Show the window containing the file. 
Dim winCount As Integer = excelObj.Parent.Windows.Count()
excelObj.Parent.Windows(winCount).Visible = True 

' Insert additional code to manipulate the test.xls file here. 
' ...

excelObj = Nothing 
End Sub

Everything is okey when Option Strict Off

enter image description here

Everything is not okey when Option Strict On

enter image description here

So, how to solve that errors when Option Strict On?

Be careful! I want to get specific excel file from the same excel instance.

Upvotes: 1

Views: 763

Answers (2)

Slai
Slai

Reputation: 22876

As a side note, there is no need to depend on the Interop.Excel early binding, if you know that it works. You can change the Error Notifications to Warning and still compile and run. 

' Imports Microsoft.Office.Interop.Excel

Dim fileName = "c:\vb\test.xls"   
If Not IO.File.Exists(fileName) Then MsgBox(fileName & " does not exist") : Exit Sub 

Dim obj = GetObject(fileName, "Excel.Application")
Dim wb = TryCast(obj, Workbook)

wb.Application.Visible = True 
wb.Windows(1).Visible = True

Upvotes: 0

TnTinMn
TnTinMn

Reputation: 11801

+1 for striving to use Option Strict On. :)

However, the code is using what is known as Late Binding and that requires you have Option Strict Off. You can however, minimize the scope of Option Strict Off by creating a new code file and using a Partial Class definition to contain the code that needs Late Binding.

You might be able to get by using the VB CallByName function with Option Strict On, but that would get ugly real quick and probably be very slow.

There also is an advanced technique using native API's that is called COM reflection that should work with Option Strict On. This technique is described in the article: [Basic Instincts - Inspecting COM Objects with Reflection].(https://msdn.microsoft.com/en-us/magazine/dd347981.aspx).

The typical method for early binding (Option Strict On) is to add a reference to the Excel primary interop assembly. This technique has some detractors as well, but is by far the easiest method.

Edit: The following demonstrates how to use the Excel PIA's and early binding to open a workbook directly similar to the OP's original code.

Dim wbPath As String = "*** replace with path to your workbook ***"
Dim wb As Excel.Workbook = CType(GetObject(wbPath), Excel.Workbook)
'or 
'Dim wb As Excel.Workbook =  CType(Marshal.BindToMoniker(wbPath), Excel.Workbook)

Dim app As Excel.Application = wb.Application
app.Visible = True
wb.Windows(1).Visible = True

Upvotes: 1

Related Questions