locked
[VB.Net] Public Member 'Open' of type 'WorksheetClass' cannot be found RRS feed

  • Question

  • Hello everybody

    On my code I've got an error on one of my line which is: Public Member 'Open' of type 'WorksheetClass' cannot be found

    The line is:

            If (doc.Open(nomFichierXLS)) Then

    So if I clearly understood, the error is on Open, right?
    But how can I correct it? What do I have to make Public? doc or nomfichierXLS?
    And how can I make it?

    Best regards,
    Nicolas

    Thursday, July 28, 2016 7:52 AM

Answers

  • Hi,

    >>What I want to do: I have an XLS file with datas inside I want to extract, make calculations, and convert into another format.

     

    I now know a little about your requirement. You can refer to the code below.

    However, I am still not clear about the Function ouvrirFichierXLSCATIA you shared.

    What you want to do by creating this function?

    If you want to open a workbook, try doc = xlapp.Application.Workbooks.Open(fileName)

    You are using if then, do you want to judge if there is a file opened? 

            Dim xlapp As Excel.Application
            Dim doc As Excel.Workbook
            Dim xlws As Excel.Worksheet
            Dim racine, racine2 As Excel.Range
            Dim openFileDialog1 As New Windows.Forms.OpenFileDialog()
            Dim fileName As String
            'get the Excel app
            xlapp = CreateObject("Excel.Application")
            xlapp.Visible = True
            'choosing the excel file
            openFileDialog1.Filter = "All Excel Files|*.xls;*.xlsx|All Files|*.*"
            openFileDialog1.Title = "Input data file"
            If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                fileName = openFileDialog1.FileName
                'opening file
                doc = xlapp.Application.Workbooks.Open(fileName)
                'get the sheet
                xlws = doc.ActiveSheet
                'get the range, or you can call a function
                racine = xlws.UsedRange
                racine2 = racine.Columns
    
                'save as the format you need 
                doc.SaveAs("New.xml")
            End If


    Thursday, July 28, 2016 9:32 AM

  • According to Workbook.MergeWorkbook Method (Object), it doesn't create a value for your case, because you are converting the format instead of merging. 

    The same issue has been resolved. See [VB.Net] Replace Open methods for Excel?


    Thursday, July 28, 2016 12:24 PM

All replies

  • Hi Nicolas,

    >>Public Member 'Open' of type 'WorksheetClass' cannot be found

    There is no Open method in WorksheetClass members.

     What do you want to open actually? I suggest you share more code with us. We couldn’t offer useful help if you just provide one line.

    Thursday, July 28, 2016 8:24 AM
  • Hello Hibari,

    Actually, I'm trying to correct my code after the solution you gave me before (that's why I din't answered on the previous subject since I still trying to make my code working ^^ )

    What I want to do: I have an XLS file with datas inside I want to extract, make calculations, and convert into another format.
    So I have an OpenFileDialog where I select my XLS File.
    After, I have a Sub like this:

        Private Function ouvrirFichierXLSCATIA(ByRef nomFichierXLS As Object) As Object
            If (doc.Open(nomFichierXLS)) Then
                ouvrirFichierXLSCATIA = True
            Else
                ouvrirFichierXLSCATIA = False
            End If
            Exit Function
            '''''
        End Function

    Thanks for you help


    Thursday, July 28, 2016 8:36 AM
  • Hi,

    >>What I want to do: I have an XLS file with datas inside I want to extract, make calculations, and convert into another format.

     

    I now know a little about your requirement. You can refer to the code below.

    However, I am still not clear about the Function ouvrirFichierXLSCATIA you shared.

    What you want to do by creating this function?

    If you want to open a workbook, try doc = xlapp.Application.Workbooks.Open(fileName)

    You are using if then, do you want to judge if there is a file opened? 

            Dim xlapp As Excel.Application
            Dim doc As Excel.Workbook
            Dim xlws As Excel.Worksheet
            Dim racine, racine2 As Excel.Range
            Dim openFileDialog1 As New Windows.Forms.OpenFileDialog()
            Dim fileName As String
            'get the Excel app
            xlapp = CreateObject("Excel.Application")
            xlapp.Visible = True
            'choosing the excel file
            openFileDialog1.Filter = "All Excel Files|*.xls;*.xlsx|All Files|*.*"
            openFileDialog1.Title = "Input data file"
            If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                fileName = openFileDialog1.FileName
                'opening file
                doc = xlapp.Application.Workbooks.Open(fileName)
                'get the sheet
                xlws = doc.ActiveSheet
                'get the range, or you can call a function
                racine = xlws.UsedRange
                racine2 = racine.Columns
    
                'save as the format you need 
                doc.SaveAs("New.xml")
            End If


    Thursday, July 28, 2016 9:32 AM
  • in fact, I'm working on an already existing code, I'm just updating it.
    Previously, the code was working on VB6, and the entry format was XML, now I'm working on VB.Net and the entry file is XLS.

    The ouvrirFichierXLSCATIA function will be used later on another sub like this:

                        If (ouvrirFichierXLSCATIA(nomFichierXLS)) Then
    
                            elemList = doc.UsedRange
    ' Rest of my code

    And during the writing of the new format file.

    And about the opening of the workbook, yes, I tried it that way, thanks

    Thursday, July 28, 2016 9:50 AM
  • Would the command doc.MergeWorkbook somehow works for this?

    It's saying that it doesn't create a value, so i guess I can't use it in a IF condition, but if this command is the way to go, I may find a way to do it?

    What do you think about it?


    Thursday, July 28, 2016 12:07 PM

  • According to Workbook.MergeWorkbook Method (Object), it doesn't create a value for your case, because you are converting the format instead of merging. 

    The same issue has been resolved. See [VB.Net] Replace Open methods for Excel?


    Thursday, July 28, 2016 12:24 PM