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-27 10:53:08Z

1

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

edited May 27, 2015 at 10:53

answered May 26, 2015 at 12:43

Josh

1,0931 gold badge7 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ProgrammingRookie Over a year ago

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!

Josh Over a year ago

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

ProgrammingRookie Over a year ago

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.

Josh Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Error when adding New Excel.application in VB .NET

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related