Results 1 to 10 of 10

Thread: I am getting either the Errors in ListView1_Click Event

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    89

    I am getting either the Errors in ListView1_Click Event

    Hello

    I am getting either the Errors in ListView1_Click Event
    Code:
    Public workBookNames(0 To 3) As String
        Public PathName(0 To 3) As String
    
        Private Sub FormXL_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        Dim myExcel As New Microsoft.Office.Interop.Excel.Application()
        Dim i As Integer
        
            workBookNames(0) = "C:\ABC\1.xlsm"
            workBookNames(1) = "C:\ABC\1A.xlsm"
            workBookNames(2) = "C:\XYZ\X2.xlsm"
            workBookNames(3) = "C:\TRIAL\2.xlsm"
    
            With ListView1
                .Columns(0).Width = 300
                .Columns(0).Text = "Workbook Path"        
                 For i = 0 To 3
                    .Items.Add(workBookNames(i))
                Next i
            End With
            With ListView2
                .Columns(0).Width = 200
            End With
        
        End Sub
    
    
        Private Sub ListView1_Click(sender As Object, e As EventArgs) Handles ListViewWorkBooks.Click
            Dim myExcel As Microsoft.Office.Interop.Excel.Application
            Dim Wks As Microsoft.Office.Interop.Excel.Worksheet
            'Dim itemWrkbook As New ListViewItem
            'Dim item As ComctlLib.ListItem
    
            ListView2.Clear()
            
          For Each Wks In myExcel.Workbooks(ListViewWorkBooks.SelectedItems(0).Text).Worksheets
    
    1.      I get Following Error if i use the above For Each with myExcel......
          System.NullReferenceException: Object reference not to an instance of an object 
    
    THEN USING BELOW as Another Option
     For Each Wks In Workbooks(ListViewWorkBooks.SelectedItems(0).Text).Worksheets
    
    2.    I get Following Error : Workbooks is an interface type and cannot be used as an Expression
          
            ListView2.Items.Add(text:=Wks.Name)
          Next
    
        End Sub
    I tried the above code adapting from https://stackoverflow.com/questions/...ead-of-listbox
    Also i did not understand why the Programmer used Dim item As ComctlLib.ListItem and where did he get that from.
    Is this really required if not then how can someone help me to correct the ListView1 Click Event

    SamD
    70
    Last edited by SamDsouza; Jan 16th, 2022 at 10:18 AM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,811

    Re: I am getting either the Errors in ListView1_Click Event

    Your link is for VBA (rather than VB.Net), so various things are different - including the fact that there is a completely different ListView control (the VBA one has ComctlLib.ListItem as a data type for items, in VB.Net it is different).

    The second error you had is because the code is not logically valid (Workbooks don't exist independently, only inside an Excel application object), so you should be using the first one instead.

    The first error you had ("Object reference not set to an instance of an object") is a fairly standard one, and it is caused by the fact that one or more items on that line has not been set up properly yet. In this case you have declared the variable myExcel , but you haven't assigned it a value (eg: myExcel = New Excel.Application ), so that would cause the error.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    89

    Re: I am getting either the Errors in ListView1_Click Event

    Si_The_Geek

    Thanks for the Prompt reply
    So over came the Error
    by using
    myExcel = New Microsoft.Office.Interop.Excel.Application
    but now new Error
    System.RunTime.InterOp.Services.COM.Exceeption Invalid Index
    at Following line For Each Wks In Workbooks(ListViewWorkBooks.SelectedItems(0).Text).Worksheets

    Also Worksheet Names are not displayed in List2
    Also time consuming

    SamD
    71

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,811

    Re: I am getting either the Errors in ListView1_Click Event

    The Workbooks collection only contains the files that are already open in that instance of Excel, and you haven't opened any.

    What you should do is open the relevant workbook, work with it, and close it again. The code for that would be something like this:
    Code:
    Dim WkBook = myExcel.Workbooks.Open(ListViewWorkBooks.SelectedItems(0).Text)
    For Each Wks In WkBook.Worksheets
       ...
    Next
    WkBook.Close

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    89

    Re: I am getting either the Errors in ListView1_Click Event

    Si_the_geek
    Code:
    Dim WkBook = myExcel.Workbooks.Open(ListViewWorkBooks.SelectedItems(0).Text)
    For Each Wks In WkBook.Worksheets
       ...
    Next
    WkBook.Close
    Thank you so much for your valuable input.
    I was wondering any code for Faster Method to display the Sheet Names.

    SamD
    72

  6. #6
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,104

    Re: I am getting either the Errors in ListView1_Click Event

    Do you only want the names of the sheets in an Excel file?

    An XLSX file is just an ordinary ZIP file with a folder structure and a bunch of XML files.
    If you rename a .XLSX file to .ZIP then use your favorite ZIP tool the open it.

    You will see a folder "xl"
    In the "xl" folder is a file named "workbook.xml"
    If you open this file then in the <sheets> section you can find the sheet names of the workbook.
    Code:
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
    <fileVersion appName="xl" lastEdited="4" lowestEdited="4" rupBuild="4506"/>
    <workbookPr defaultThemeVersion="124226"/>
    <bookViews>
    <workbookView xWindow="120" yWindow="60" windowWidth="23775" windowHeight="14505"/>
    </bookViews>
    <sheets>
    <sheet name="Sheet1" sheetId="1" r:id="rId1"/>
    <sheet name="Sheet2" sheetId="2" r:id="rId2"/>
    </sheets>
    <calcPr calcId="125725"/>
    </workbook>

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    89

    Re: I am getting either the Errors in ListView1_Click Event

    Arnoutdv

    Yes just wanted list of Sheet names from Listview control

    I mean with below Default syntax
    Dim WkBook = myExcel.Workbooks.Open(............
    WkBook.Close
    Workbook Opens... Display names.... and Workbook Closes . I think this causes the slowness. So was wondering. if by any other method if above could be achieved faster. or Can we use with Ctype...... to display the sheet names.

    SamD
    73

  8. #8
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,104

    Re: I am getting either the Errors in ListView1_Click Event

    One option is to use Excel Automation, as you already tried

    The other option is to treat the Excel as if it's a ZIP file.
    Extract the "xl\workbook.xml" to memory and parse the XML.
    https://stackoverflow.com/questions/...emory-not-disk

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,868

    Re: I am getting either the Errors in ListView1_Click Event

    I would go with OLEDB, I haven't tried Arnoutdv way.

    @Sam
    if you have Excel then I assume you have Access
    give this a try with GetOleDbSchemaTable
    Code:
    Option Strict On
    Imports System.Data.OleDb
    Imports System.IO
    
    Public Class Form2
    
        Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            With ListView1
                .View = View.Details
                '.LabelEdit = False
                '.HideSelection = False
                .GridLines = True
                .CheckBoxes = True
                .FullRowSelect = True
                .Columns.Add("Excel-Path", 320)
                .Columns.Add("Sheetname", 120)
            End With
        End Sub
    
        Public Sub lvwAddItem(ByVal lvw As ListView, ByVal ParamArray Text() As String)
            With lvw.Items
                .Add(New ListViewItem(Text))
            End With
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim di As New DirectoryInfo("E:\Berichte\Exceltest")
            Dim ExcelFiles As FileInfo() = di.GetFiles("*.xls*")
            Dim fi As System.IO.FileSystemInfo
            For Each fi In ExcelFiles
                Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fi.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES'")
                    con.Open()
                    Dim sheets As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    'Excelfiles from Folder with Sheetnames
                    For Each sheet As DataRow In sheets.Rows
                        Dim tableName As String = sheet("TABLE_NAME").ToString()
                        ' Debug.WriteLine(fi.FullName & ";" & tableName)
                        lvwAddItem(ListView1, fi.FullName, tableName)
                    Next
                End Using
            Next
        End Sub
    End Class
    this will read the Directory and add the Excelfile with Sheetnames to a Listview
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    89

    Re: I am getting either the Errors in ListView1_Click Event

    Sorry Chris
    I get the following error
    System.InvalidOperationException: 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

    at following line

    Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fi.FullName & ";Extended Properties='Excel 12.0 Xml;HDR=YES'")
    Even in Project>Reference i get "System"

    there are 2 different
    system 2.4 File version 14.8.4084.0
    system 2.0 File version 8.0.50727.9148

    So what will you suggest ?
    SamD
    74
    Last edited by SamDsouza; Jan 22nd, 2022 at 04:56 AM.

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