-
Jun 17th, 2021, 10:47 AM
#1
Thread Starter
Addicted Member
Any better method to incorporate particular Range from Excel Sheet to ListView
Hello
Would like to have some guidance as to which method to adopt for incorporating particular Range from Excel Sheet to Listview.
Is It that i need to create new data table for range of colums and rows, Get Range address and on basis of range address. the Listview is filled up with respective Rows and Colums.
Because i tried to achieve the above really not in an efficient manner.
1. ComboBox and Texbox values are uploaded
2. Clicking on ComboBox value respectve DataTable index Value item is displayed in ComboBox and Textbox..
3. So far working smoothly ie. Click on ComboBox Item which is loaded correctly with Respective Textbox Data displayed with the value of DataTable index
Rather not a good step i took from step 4 onwards
4. As I wanted to update Listview Object/Control First i tried to MAtch the value in comboBox which is also in the Range of Excel sheet and getting the Range address
5. Button Was created to update the listView that was with which result was displayed correctly. But it takes more minutes to get the range uploaded in the Listview.
6. Secondly When Typing second value in combox the listView did not update correctly
7. Why does it take so much of time to update Listview Object just for a range of Worksheet ?
Any better methods to work on to achieve the following
Rather than Button Object to function I would prefer Clicking a Combobox or Enter or Dblclick pressed and to upload the Listview for Particular Range of Worksheet
on that second it displays the range in Listview
Eg if typed Fruits in combobox then Sheet5 contains B coloum with Value "Fruits" ie from B3:B9 and From Range B3:E9 it displays the other values
Thanks in advance
SamD
Thread 7 :892284
34
-
Jun 18th, 2021, 12:11 AM
#2
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
SamD
Thread 2: 892141 :
15
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.
-
Jun 18th, 2021, 09:33 AM
#3
Thread Starter
Addicted Member
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
ChrisE
SamD
Thread 2: 892141 :
15
Indeed a Good One HaHa
FYI adopted, from the above thread and planning to change.
Before uploading the lisView object thought of getting range address
The logic goes like
Get Fruits, Things from one Column of sheet1 and the names of from the other Sheet where column with "Fruits", "Things" wiil be there in another sheet.
Unfortunately i get Error
System.Runtime.InteropServices.InvalidComObjectException: 'COM object that has been separated from its underlying RCW cannot be used.'
It seems i cannot use the Two or More different sheets at a time of same workbook if i've understood the above error correctly.
Because to create columns in ListView will have different set of columns. So i thought of creating Two sheets . in case this type of requirement could crop up in future.
One sheet containg column with Fruits, Things etc and another sheet "sheet5" containing Name of Different Fruits , stock, Rate etc
Name of things, thier stock and rates etc.
With Thread 2 892141: 15 at least We could read the records of combobox item and other values in different textboxes.
Now with same combobox to get the range of another worksheet is Sheet5 with data. so that the same can be uploaded in Listview
Code:
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class ufListView2
Public myExcel As New Excel.Application
Public dt As New System.Data.DataTable
Public dtLstVu As New System.Data.DataTable
Public xlWorkbook As Excel.Workbook
Public xlWks1 As Excel.Worksheet
Public xlWks5 As Excel.Worksheet
Private Sub ufListView2_Load(sender As Object, e As EventArgs) Handles MyBase.Load
myExcel = New Excel.Application
strFilename = "C:\ABCD\Trial\LstVu2Data.xlsx"
xlWorkbook = myExcel.Workbooks.Open(strFilename)
SheetNamePlease = "Sheet1"
SheetName5Please = "Sheet5"
With combo1
.DropDownStyle = ComboBoxStyle.DropDown
.AutoCompleteMode = AutoCompleteMode.Suggest
.AutoCompleteSource = AutoCompleteSource.ListItems
End With
PullExcel()
AddToCombobox()
Me.Update()
Sub PullExcel()
dt.Clear() 'clears it out so we can start fresh
dt.Columns.Add("ComboBox1", GetType(String))
Dim cmb1Bx As String = "My ComboBox"
Dim rngAddressBx As String = "My Textbox"
For i As Long = 2 To 73
Try
rng = xlWks1.Range("B" & i)
cmb1Bx = CStr(rng.Value)
If cmb1Bx = "" Then Exit For
ReleaseCOM(rng)
dt.Rows.Add(cmb1Bx)
Catch ex As Exception
Exit For
End Try
Next
Try
xlWorkbook.Close()
Catch ex As Exception
End Try
Next
releaseObject(xlWks1)
ReleaseCOM(xlWks1)
releaseObject(xlWks5)
ReleaseCOM(xlWks5)
releaseObject(xlWorkbook)
ReleaseCOM(xlWorkbook)
releaseObject(myExcel)
ReleaseCOM(myExcel)
End Sub
Sub AddToCombobox()
With combo1.Items
.Clear()
For i = 0 To dt.Rows.Count - 1
.Add(dt.Rows(i).Item(0))
Next
End With
End sub
Public Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
Try
If COMObj IsNot Nothing Then
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
End If
Finally
COMObj = Nothing
If GCCollect Then
GC.WaitForPendingFinalizers()
GC.Collect()
End If
End Try
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Sub Combo1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cmbProdCode.SelectedIndexChanged
If Combo1.Items.Contains(Combo1.Text) Then
getAddressRange()
End If
End Sub
Sub getAddressRange()
Dim currentFind As Microsoft.Office.Interop.Excel.Range = Nothing
Dim ItemSrchdRng As String
With xlWks5
Dim ItemColRng As Microsoft.Office.Interop.Excel.Range = .Columns(2)
System.Runtime.InteropServices.InvalidComObjectException: 'COM object that has been separated from its underlying RCW cannot be used.'
currentFind = ItemColRng.Find(Combo1.Text,, XlFindLookIn.xlValues, XlLookAt.xlWhole, , XlSearchDirection.xlNext, False)
If Not currentFind Is Nothing Then
ItemSrchdRng = currentFind.Resize(myExcel.WorksheetFunction.CountIf(ItemColRng, Combo1.Text), 4).Address(0, 0).ToString
txtRngAdd.Text = ItemSrchdRng
Else
'''''
End If
End With
End Sub
SamD
Thread 7 :892284
35
-
Jun 19th, 2021, 12:27 AM
#4
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
I allready gave you a sample(s) to load and filter Excel Data with oleDB
Code:
Option Strict On
Public Class Form1
Public Function ExcelOleDb(ByVal strTextPath As String, _
ByVal sSQL As String) As System.Data.DataTable
Dim con As New System.Data.OleDb.OleDbConnection
Dim myCmd As New System.Data.OleDb.OleDbCommand
Dim myadp As New System.Data.OleDb.OleDbDataAdapter
Dim mydt As New System.Data.DataTable
With con
.ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
.ConnectionString &= "data source=" & strTextPath & ";"
.ConnectionString &= "Extended Properties = ""Excel 12.0 XML"";"
End With
With myCmd
.Connection = con
.CommandType = CommandType.Text
.CommandText = sSQL
End With
With myadp
.SelectCommand = myCmd
Try
.Fill(mydt)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End With
Return (mydt)
End Function
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
'Sample.1) select all:
DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Sheet1$]")
'###############################
'Sample.2) select Range: A1:C4 and show in Datagridview
'DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Sheet1$A1:C4]")
'################################
'Fill the ComboBox with Distinct Product names
ComboBox1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT Distinct Product FROM [Sheet1$A:A]")
ComboBox1.DisplayMember = "Product"
End Sub
Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
'Sample.3) Filter Data with Combobox and show in Datagridview
DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * From [Sheet1$] Where Product Like '" & ComboBox1.Text & "'")
End Sub
End Class
you should consider loading the excel data to Database(Access;MySQL or...) and work from there.
it is just going to get more and more complicated as you go along, trying to follow the Path you have in your head...
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.
-
Jun 19th, 2021, 11:40 AM
#5
Thread Starter
Addicted Member
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
ChrisE
Code:
Option Strict On
Public Class Form1
Public Function ExcelOleDb(ByVal strTextPath As String, _
ByVal sSQL As String) As System.Data.DataTable
Dim con As New System.Data.OleDb.OleDbConnection
Dim myCmd As New System.Data.OleDb.OleDbCommand
Dim myadp As New System.Data.OleDb.OleDbDataAdapter
Dim mydt As New System.Data.DataTable
With con .............
Really appreciate your efforts in pushing me and me to move further.
Problem is that i am not at familar with oleDB and did not have any oppurtunity to work with
need really sometime for me to really abosrb the Structure, Enviornment and the result presentation with oleDB
Kindly excuse me
Will revert back but Need Some time.
SamD
Thread 7 :892284
36
-
Jun 19th, 2021, 12:49 PM
#6
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
Originally Posted by SamDsouza
ChrisE
Code:
Option Strict On
Public Class Form1
Public Function ExcelOleDb(ByVal strTextPath As String, _
ByVal sSQL As String) As System.Data.DataTable
Dim con As New System.Data.OleDb.OleDbConnection
Dim myCmd As New System.Data.OleDb.OleDbCommand
Dim myadp As New System.Data.OleDb.OleDbDataAdapter
Dim mydt As New System.Data.DataTable
With con .............
Really appreciate your efforts in pushing me and me to move further.
Problem is that i am not at familar with oleDB and did not have any oppurtunity to work with
need really sometime for me to really abosrb the Structure, Enviornment and the result presentation with oleDB
Kindly excuse me
Will revert back but Need Some time.
SamD
Thread 7 :892284
36
You asked if there was a better way than that you’re using, which is exactly what ChrisE has given you…
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 20th, 2021, 02:10 AM
#7
Thread Starter
Addicted Member
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
ChrisE and Paul
I get following message
The Microsoft.ACE.OLEDB.12.0' provider is not registered on the Local Machine as Per MessageBox.Show(ex.Message)
What needs to be done for the following
Code:
.ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
In Project> Reference i found the below any thing needs to be added
Microsoft OLE DB Service Component 1.0 Type Library
Microsoft OLE DB Simple Provider 1.0 Type Library
Do i need to Tick mark of the above option inorder to Function smoothly
OR
under Tools>Connect To DataBase> Which option i select
MS Access Database File
MS ODBC DataSource
MS SQL-Server
MS SQL-Server DataBase File
Oracle DataBase
<Other>
If required which option needs to be selected for the Connection String.
If any other thing is missing or needs to be refered you may to guide me step by step for other option in VS2019. As i am not familar with OLEDB.
SamD
Thread 7 :892284
37
Last edited by SamDsouza; Jun 20th, 2021 at 02:14 AM.
-
Jun 20th, 2021, 04:00 AM
#8
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
Project-->Properties-->Compile-->Advanced Compile Options-->Target CPU
Try setting Target CPU to x86
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jun 20th, 2021, 09:33 PM
#9
Thread Starter
Addicted Member
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
Paul
Project-->Properties-->Compile-->Advanced Compile Options-->Target CPU
Try setting Target CPU to x86
Did not work
in VB.19 as per above
Project>ProjectName Properties>Compile>
In Complie Template It shows
configuration = Active (Debug) Platform(Any CPU)
Target CPU = Any CPU changed to x86
Attached image for above description
Attachment 181692
Any properties to be changed of DataGridView1
SamD
Thread 7 :892284
38
Last edited by SamDsouza; Jun 20th, 2021 at 09:36 PM.
-
Jun 21st, 2021, 07:38 AM
#10
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
It seems that you need to install the ACE provider, you can find it here:
https://www.microsoft.com/en-gb/down....aspx?id=13255
There are two versions, the x64 is for 64 bit apps, and the other (x86) is for 32 bit apps. I don't think you can install both.
-
Jun 22nd, 2021, 02:09 AM
#11
Thread Starter
Addicted Member
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
Si_the_geek
I visited there it mentions Microsoft Access Database Engine 2010 Redistributable
Is it correct ?
For Windows 10 - MS Office Home and Student Version 2013 32Bit . It seems i've to go for x86. Kindly guide me
SamD
Thread 7 :892284
39
Last edited by SamDsouza; Jun 22nd, 2021 at 02:12 AM.
-
Jun 22nd, 2021, 08:28 AM
#12
Re: Any better method to incorporate particular Range from Excel Sheet to ListView
Things like the version of Office etc don't matter (because this is a separate thing for programs to use), what matters is whether your program is 32-bit or not.
Unfortunately it isn't just your program you need to worry about, because other programs on the computer might use it too. To minimise clashing with other programs, it is probably best to use 32-bit.
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
|