locked
[VB.Net] Replace Open methods for Excel? RRS feed

  • Question

  • Hello everybody,

    I am updating a code written in VB6 and with an XML file in entry, into a VB.Net program with an XLS file in entry.

    But in this code I have a function which is

        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

    I need this function later for this king of sub:

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

    Moreover doc is dimed like that:

            Dim xlapp As Excel.Application
            Dim doc As Excel.Workbook
            xlapp = CreateObject("Excel.Application")
            xlapp.Visible = True
            doc = xlapp.Application.Workbooks.Add()
    nomfichierExcel comes from a textbox openfiledialog where I import the XLS file in a form

    But Open doesn't work with Excel application.

    How can I replace it? I thought about doc.MergeWorkbook but since it doesn't produce a value, I can't use it in an If condition.

    Any ideas?

    Best regards,
    Nicolas


    Thursday, July 28, 2016 12:44 PM

Answers

  • I thought you had resolved your issue, so I suggest you post a new thread since you said you want to use MergeWorkbook in the IF condition. Sorry, my bad. 

     

    >>doc is already a Workbook

     

    If you want to open an existing workbook, please use doc = xlapp.Application.Workbooks.Open(fileName)

    If you want to create a new workbook, please use doc = xlapp.Application.Workbooks.Add()

     

    You are using  If (doc.Open(nomFichierXLS)) Then, I wonder what nomFichierXLS  represents. 

    And It should return boolean value between IF and Then.

    So do you want to check if the nomFichierXLS (maybe it is a worksheet?) is opened in a workbook? If true then the ouvrirFichierXLSCATIA function return true? 


    Thursday, July 28, 2016 3:19 PM
  • Ok, I changed the way of doing that: when I load the worksheet with doc=xlapp.Application.Workbooks.Open(fileName), I make a new variable "dim bool As boolean" and I put it in True

    Then, on my If statement, I call the value of bool

    But now I have a new problem: after, I have a "elemlist = doc.UsedRange" and the same problem goes on now: Public Member "UsedRange" of type "WorksheetClass" cannot be found.
    So I added a doc.Worksheet.UsedRange but same error.

    How can I access the used range of my worksheet then?

    Thanks for your help anyway :)

    Best Regards

    Friday, July 29, 2016 8:26 AM
  • >>It's saying : "Public Member 'Open' of Type "WorksheetClass" cannot be found" which is weird because doc is a workbook and nomfichierXLS is an Object

    "doc" is a workbook and "nomfichierXLS" is also a workbook, you cannot use a workbook to open another one. There is no doc.Open() method, so forget the error.

     

    >>Public Member "UsedRange" of type "WorksheetClass" cannot be found.

    If you want to get the range, first you should get the worksheet.

            Dim doc As Excel.Workbook

            Dim xlws As Excel.Worksheet

            Dim elemlist As Excel.Range

     

                doc = xlapp.Application.Workbooks.Open(fileName)

                xlws = doc.ActiveSheet

                elemlist = xlws.UsedRange

    Friday, July 29, 2016 9:20 AM

All replies

  • What do you mean by "Open doesn't work with Excel application"? Are you getting an exception (error)?

    I don't see any code for Open but this method applies to the Workbooks collection.

     xlWorkbook = ExcelApplication.Workbooks.Open("C:\Users\...\Documents\My Database\Excel\Book1.xlsx")


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 28, 2016 1:57 PM
  • Yes I'v got the error : Public Member 'Open' of Type "WorksheetClass" cannot be found
    Thursday, July 28, 2016 2:13 PM
  • Yes I'v got the error : Public Member 'Open' of Type "WorksheetClass" cannot be found

    This is correct. You should be using Workbooks.Open instead.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 28, 2016 2:35 PM
  • But doc is already a Workbook, does nomfichierXLS has to be a workbook too then?
    Thursday, July 28, 2016 2:43 PM
  • I thought you had resolved your issue, so I suggest you post a new thread since you said you want to use MergeWorkbook in the IF condition. Sorry, my bad. 

     

    >>doc is already a Workbook

     

    If you want to open an existing workbook, please use doc = xlapp.Application.Workbooks.Open(fileName)

    If you want to create a new workbook, please use doc = xlapp.Application.Workbooks.Add()

     

    You are using  If (doc.Open(nomFichierXLS)) Then, I wonder what nomFichierXLS  represents. 

    And It should return boolean value between IF and Then.

    So do you want to check if the nomFichierXLS (maybe it is a worksheet?) is opened in a workbook? If true then the ouvrirFichierXLSCATIA function return true? 


    Thursday, July 28, 2016 3:19 PM
  • But doc is already a Workbook, does nomfichierXLS has to be a workbook too then?

    I'm afraid that I do not understand your question. Do you need to open a Workbook or no?

    What is the relationship between "doc" and "nomfichierXLS"?


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, July 28, 2016 3:26 PM
  • >And It should return boolean value between IF and Then.

    So do you want to check if the nomFichierXLS (maybe it is a worksheet?) is opened in a workbook? If true then the ouvrirFichierXLSCATIA function return true?

    Yes this is exactly what I want to do, check if nomfichierXLS is really opened through "doc"

    Ok, so nomfichierXLS is dimed like that: nomfichierXLS = OpenFileDialog.FileName

    Then, in the function it is dimed as an Object

    nomfichierXLS is a workbook with only 1 worksheet so I'm not sure if I have to dim it as a workbook or a worksheet

    Well the problem is on the line "doc.Open(nomfichierXLS)"
    It's saying : "Public Member 'Open' of Type "WorksheetClass" cannot be found" which is weird because doc is a workbook and nomfichierXLS is an Object

    Friday, July 29, 2016 5:57 AM
  • Ok, I changed the way of doing that: when I load the worksheet with doc=xlapp.Application.Workbooks.Open(fileName), I make a new variable "dim bool As boolean" and I put it in True

    Then, on my If statement, I call the value of bool

    But now I have a new problem: after, I have a "elemlist = doc.UsedRange" and the same problem goes on now: Public Member "UsedRange" of type "WorksheetClass" cannot be found.
    So I added a doc.Worksheet.UsedRange but same error.

    How can I access the used range of my worksheet then?

    Thanks for your help anyway :)

    Best Regards

    Friday, July 29, 2016 8:26 AM
  • >>It's saying : "Public Member 'Open' of Type "WorksheetClass" cannot be found" which is weird because doc is a workbook and nomfichierXLS is an Object

    "doc" is a workbook and "nomfichierXLS" is also a workbook, you cannot use a workbook to open another one. There is no doc.Open() method, so forget the error.

     

    >>Public Member "UsedRange" of type "WorksheetClass" cannot be found.

    If you want to get the range, first you should get the worksheet.

            Dim doc As Excel.Workbook

            Dim xlws As Excel.Worksheet

            Dim elemlist As Excel.Range

     

                doc = xlapp.Application.Workbooks.Open(fileName)

                xlws = doc.ActiveSheet

                elemlist = xlws.UsedRange

    Friday, July 29, 2016 9:20 AM
  • For the first error, I succeeded to overpass it, thank you!

    ActiveSheet! I was sure it was something like that but I didn't think of it, thank you Hibari! :)

    Friday, July 29, 2016 9:31 AM
  • Very glad that you resolve original issue. Maybe you could finish the other two threads since they are similar problem.

    If you have any other questions later, you could post a new thread.  (´・ω・`)

    Friday, July 29, 2016 10:37 AM