Error when adding New Excel.application in VB .NET

Question

For a project I have to be able to read infromation from an Excel sheet and then pass that information on to a PLC. Sadly I have a problem when I use 'Dim App As New Excel.Application' For some reason this make my application crash before the form shows itself on the screen, so that I end up with no form. The Error I get is:

An unhandled exception of type 'System.InvalidOperationException' occurred in OpHoopVanZegen.exe

This happens in the 'Application.Designer.vb'

Protected Overrides Sub OnCreateMainForm()

            Me.MainForm = Global.OpHoopVanZegen.Form1
        End Sub

^^^Where the error happens^^^

Additional info: Cannot convert COM-object from type System.__ComObject to interfacetype

Anyone that could explain me what's going on and /or what I'm doing wrong?

------------------------------------ My Form Code:

 Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Dim App As New Excel.Application
    Dim svrOPC As New OPCAutomation.OPCServer
    Dim WithEvents grpOPC As OPCAutomation.OPCGroup

Private Sub connectToOPC_Click(sender As Object, e As EventArgs) Handles connectToOPC.Click
    If svrOPC.ServerState = OPCAutomation.OPCServerState.OPCDisconnected Then
        Call svrOPC.Connect("Kepware.KEPServerEX.V5")
    End If
    Me.TBStatusOPC.Text = udfOPCServerState(svrOPC.ServerState)
End Sub
Private Sub Form1_Activated(sender As Object, e As EventArgs) Handles Me.Activated
    Me.TBStatusOPC.Text = udfOPCServerState(svrOPC.ServerState)
End Sub
Private Function udfOPCServerState(ByVal intOPCServerState As Integer) As String
    Select Case intOPCServerState
        Case OPCAutomation.OPCServerState.OPCRunning
            udfOPCServerState = "Running"
        Case OPCAutomation.OPCServerState.OPCFailed
            udfOPCServerState = "Failed"
        Case OPCAutomation.OPCServerState.OPCNoconfig
            udfOPCServerState = "No Config"
        Case OPCAutomation.OPCServerState.OPCSuspended
            udfOPCServerState = "Suspended"
        Case OPCAutomation.OPCServerState.OPCTest
            udfOPCServerState = "Test"
        Case OPCAutomation.OPCServerState.OPCDisconnected
            udfOPCServerState = "Disconnected"
        Case Else
            udfOPCServerState = "Unknown"
    End Select
End Function

Private Sub LoadOrderToPLC_Click(sender As Object, e As EventArgs) Handles LoadOrderToPLC.Click

End Sub

End Class

Josh · Accepted Answer · 2015-05-27T10:53:08.017

I never found a good reason why, but I had the same problem with a recent project. The workaround that got things functioning for me was:

Imports Microsoft.Office.Core
Dim app as Object
app = CreateObject("Excel.Application")

This made the rest of my code work as I wanted it to, but with the drawback that IntelliSense didn't function for any of my Excel variables. Since I'm a slacker who relies heavily shortcuts rather than memorizing everything, I wrote myself a commented-out syntax guide and pasted it wherever I was working with spreadsheets in my code for reference.

I hope this helps. I'm still curious if there's a better way to fix this.

EDIT: An example snippet from my own code (with my cheat sheet included):

Imports Microsoft.Office.Core

Dim xl As Object, wb As Object, sht As Object, rng As Object, wrksht As Object

    'EXCEL SYNTAX GUIDE:
    'Excel.
    '     .Application  :   Instance of the Excel program opened by VB code
    '     .Workbooks    :   All open workbooks. Often use .Add or .Open when setting Workbook variable
    '     .Workbook     :   An individual workbook (.xls file). Often use .ActiveSheet to set Worksheet variable
    '     .Sheets       :   All worksheets (tabs) of a particular workbook
    '     .Worksheet    :   An individual worksheet
    '     .Range        :   A range of worksheet cells, e.g. ("A1", "C4")

    xl = CreateObject("Excel.Application")
    xl.Visible = False
    xl.DisplayAlerts = False
    wb = xl.Workbooks.Open(Environ("USERPROFILE") & "\dropbox\Program Database.xlsx")
    For Each wrksht In wb.Worksheets
        If wrksht.Name = "MASTER MATRIX" Then
            sht = wrksht
            Exit For
        End If
    Next
    For rNum = 6 To 201
        rng = sht.Range("A" & rNum, "AT" & rNum)
        Dim inputs(12) As String
        inputs(0) = rng.Cells(1, 1).Value
        inputs(1) = rng.Cells(1, 4).Value
        inputs(2) = rng.Cells(1, 5).Value
'This should be enough to demonstrate all the necessary syntax.
'
'
'Release variables and close Excel as follows:
'(Disabling alerts and user control prevents Excel from asking the user to save)
rng = Nothing
sht = Nothing
wb = Nothing
xl.DisplayAlerts = False
xl.UserControl = False
xl.Quit()
xl = Nothing

Hey, I tried this piece of code, but have no idea how it should be working, I cannot seem to use any of the available methods that I have whenever I import microsoft.office.interop.excel. Any explanation you have how I could implement this to make sure I can read excel files from visual basic? Thanks for ur time and effort! — ProgrammingRookie, May 26 '15 at 17:01
@ProgrammingRookie, From my experience, once you import Microsoft.Office.Core and declare your application variable as above (the last 2 lines of my code **instead of** Dim app As New Excel.Application), the rest of the code compiles exactly as it would have using the Microsoft.Office.Interop.Excel namespace. For reference, here is a segment of the code from my project: (I put it as an edit in my answer as formatting the comment was a pain.) — Josh, May 26 '15 at 17:37
Thank you very much! The example you gave me really helped me, and I am now able to read from excel again. I have one final question, since you open an excel application in visual basic, how do I close this application?? Because otherwise I end up with like 10 excel processes running. — ProgrammingRookie, May 27 '15 at 07:40
Good question. Obviously, you can take care of that manually with Task Manager (not a good solution, but usually necessary during debugging). Calling `.Quit()` on your application variable will take care of this, but it is good programming practice to also release all of your Excel variables to avoid memory leaks. See my edit above. — Josh, May 27 '15 at 10:49
Note that you can also use `Using` blocks for the Excel variables. For some reason I've never developed this habit and still do things manually. — Josh, May 27 '15 at 10:57

Error when adding New Excel.application in VB .NET

1 Answers1