Results 1 to 4 of 4

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2014
    Posts
    5

    Lightbulb 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

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    do you have a reference to excel?
    also you do not appear to have any open workbook in either of your new excel instances
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2014
    Posts
    5

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

    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

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

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

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width