-
Jan 16th, 2022, 10:09 AM
#1
Thread Starter
Addicted Member
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.
-
Jan 16th, 2022, 10:18 AM
#2
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.
-
Jan 16th, 2022, 10:41 AM
#3
Thread Starter
Addicted Member
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
-
Jan 16th, 2022, 12:09 PM
#4
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
-
Jan 16th, 2022, 11:15 PM
#5
Thread Starter
Addicted Member
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
-
Jan 17th, 2022, 03:33 AM
#6
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>
-
Jan 19th, 2022, 06:49 AM
#7
Thread Starter
Addicted Member
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
-
Jan 19th, 2022, 07:05 AM
#8
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
-
Jan 19th, 2022, 07:48 AM
#9
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.
-
Jan 22nd, 2022, 04:53 AM
#10
Thread Starter
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|