none
VB.Net 2013 Excel workbook object created by class limits properties / methods RRS feed

  • Question

  • The intention of my code is to use a class cWB to open workbooks and return a full-function Workbook object.

    My problem is that the oBook object created and returned by my class, does not give me access to the

    full property/method set of an Excel.Workbook.

    For example using oBook.Sheets gives the Intellisense message: 'Error: 'Sheets' is not a member of cWB

    Any suggestions appreciated.

    Imports Excel = Microsoft.Office.Interop.Excel
    Public Class cWB
        Public Property IsOpen As Boolean
        Public Property oBook As Excel.Workbook

        Sub New(ByVal xlApp As Excel.Application, ByVal filename As String)

            Try
                Me.oBook = xlApp.Workbooks.Open(Filename:=filename, ReadOnly:=True)
                Me.IsOpen = True
                Me.oBook = oBook
                Exit Sub

            Catch ex As Exception
                Me.IsOpen = False
                Me.oBook = Nothing
            End Try

        End Sub

        Sub Close()
            Me.oBook.Close()
            Me.oBook = Nothing
        End Sub
    End Class

    Imports Excel = Microsoft.Office.Interop.Excel
    Module Module1
        Sub Main()
            Dim xlApp As New Excel.Application
            Dim filename As String = "D:\workbook.xls"
            Dim oBook As New cWB(xlApp, filename)

            Try
                If oBook.IsOpen = True Then
                    Console.WriteLine("oBook.oBook=" & oBook.oBook.Name _
                                      & " now open=" & oBook.IsOpen)

                    'Error: 'Sheets' is not a member of cWB
                    'Console.WriteLine(oBook.Sheets(1).Cells(1, 1).Value())

                Else
                    Console.WriteLine("oBook.oBook=" & oBook.oBook.Name _
                                      & " now open=" & oBook.IsOpen)
                End If

            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

            Console.WriteLine("Cleaning up")
            oBook.Close()
            Console.ReadLine()

            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(oBook)
        End Sub


    • Moved by Grady_Dong Monday, August 31, 2015 2:12 AM Not Related WCF
    Sunday, August 30, 2015 4:56 AM

Answers

  • Hi emenetvb,

    The instance of cWB( oBook) is an custom class instead of Workbook class. There is no definition for the member sheets in the custom class.

    To fix this issue, we can use the Workbook class from Office PIAs instead wrap it.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 1, 2015 5:11 AM
    Moderator

All replies

  • This is the WCF forum that you are posting to.
    Sunday, August 30, 2015 7:24 AM
  • Hi emenetvb,

    According to your description, I thought this case is not related WCF, so I will move this  case to

    Excel dev Forum. And trying  to find  a batter way  to solve this problem.

    Best Regards,

    Grady

    Monday, August 31, 2015 2:10 AM
  • Hello,

    Try to use the Worksheets property instead. It returns a Sheets collection that represents all the worksheets in the specified workbook.

    Monday, August 31, 2015 7:41 AM
  • Hi emenetvb,

    The instance of cWB( oBook) is an custom class instead of Workbook class. There is no definition for the member sheets in the custom class.

    To fix this issue, we can use the Workbook class from Office PIAs instead wrap it.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 1, 2015 5:11 AM
    Moderator