I am getting either the Errors in ListView1_Click Event

[SamDsouza]

Hello

I am getting either the Errors in ListView1_Click Event

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

[si_the_geek]

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.

[SamDsouza]

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

[si_the_geek]

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:

Dim WkBook = myExcel.Workbooks.Open(ListViewWorkBooks.SelectedItems(0).Text)
For Each Wks In WkBook.Worksheets
   ...
Next
WkBook.Close

[SamDsouza]

Si_the_geek

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

[Arnoutdv]

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.

<?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>

[SamDsouza]

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

[Arnoutdv]

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

[ChrisE]

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

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

[SamDsouza]

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