Results 1 to 12 of 12

Thread: Any better method to incorporate particular Range from Excel Sheet to ListView

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    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

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

    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.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    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

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

    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.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    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

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,033

    Re: Any better method to incorporate particular Range from Excel Sheet to ListView

    Quote Originally Posted by SamDsouza View Post
    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…

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    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.

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,033

    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

  9. #9

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    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.

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

    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.

  11. #11

    Thread Starter
    Lively Member
    Join Date
    May 2021
    Posts
    65

    Re: Any better method to incorporate particular Range from Excel Sheet to ListView

    Si_the_geek
    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.
    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.

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

    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
  •  



Click Here to Expand Forum to Full Width