Query on Office Automation using VB.Net - Creating object for Excel using VB.Net

Hello All,

I am trying to develop a dll file using Visual Studio 2013 Ultimate for automating Excel.

While trying to do so, I receive a big error message while declaring object for workbook.

Request you to provide some piece of code to declare object for Excel application and Excel workbook and then to get the count of excel workbooks opened.

Below is the sample code.

Code:

---

Imports System.Runtime.InteropServices
Imports System.Text
Imports Microsoft.Win32

Public Class ATOM

    <DllImport("user32.dll", SetLastError:=True)> _
    Private Shared Function GetWindowThreadProcessId(ByVal hwnd As IntPtr, _
                                                    ByRef lpdwProcessId As Integer) As Integer
    End Function
    Public Function Click()
        Dim ExcelApp As New Microsoft.Office.Interop.Excel.Application
        Dim diffexcelapp As New Microsoft.Office.Interop.Excel.Application
        Dim wb As New Microsoft.Office.Interop.Excel.Workbook
        MsgBox(diffexcelapp.Workbooks.Count)
        MsgBox(ExcelApp.Workbooks.Count)

    End Function
End Class

---

Below is the error message i receive at the line of

Dim wb As New Microsoft.Office.Interop.Excel.Workbook

"An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in PDUNZDLL.dll

Additional information: Retrieving the COM class factory for component with CLSID {00020819-0000-0000-C000-000000000046} failed due to the following error: 80040154."

Thanks in Anticipation

do you have a reference to excel?
also you do not appear to have any open workbook in either of your new excel instances

Yes i had reference Microsoft Excel 14.0 Object library.

I had opened excel workbooks manually and i just want to get the count of number of workbooks currently opened in the system.

We usually do it in VBA using Application.workbooks.count

Quote:

---

I had opened excel workbooks manually and i just want to get the count of number of workbooks currently opened in the system.

---

as you are creating a new instance of excel it will have no open workbooks
to work with an existing instance of excel you should use getobject, preferably with late binding (no reference)
i am not sure of the correct syntax for this in vb.net, but you should be able to find it easy enough
in vb /vba it would be

Code:

---

set xl = getobject(, "excel.application")

---

you certainly should not use the set keyword in .net