[RESOLVED] Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 questions.
Hello
Can anyone help me to refine the AutoComplete Search Comobbox from VBA to VB.NET19
Following is the code in VBA-Excel for Searching particular item from a Range of Column and same being loaded in the Combobox2.
In VBA Excel Combobox as Object had many limited features and so smoothness was not seen while executing the same.
But under the circumstances Functionalbe in VBA
The same type of adaption but with some beautiful functionality i want to derive for a combobox ie data from column B of a worksheet in to Combbobox of .Net Userform and respective row data into respective textbox.
Following VBA Code uploads the Data from Col B of Worksheet 5 in Combobox2 of VBA userform
With ComboBox2_Click Event it displays the respective data of diffrent columns into respective Textboxes of that particular row
Code:
VBA Code
Option Explicit
Public FullList As Variant, lastRow As Long
Public IsArrow As Boolean
Private Sub UserForm_Initialize()
Dim Wks As Worksheet, myArray As Variant
Dim lastRow As Long
Set Wks = Worksheets("SHEET5")
lastRow = Worksheets("SHEET5").Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets("SHEET5")
.Activate
myArray = .Range("A2:P" & lastRow).Value
ComboBox3.List() = .Range(Cells(2, 1), Cells(lastRow, 1)).Value
ComboBox2.List() = .Range(Cells(2, 2), Cells(lastRow, 2)).Value
End With
End Sub
Private Sub ComboBox2_Change()
Dim recFound As Boolean, totRowsCount As Long
Dim i As Long
lastRow = Worksheets("SHEET5").Cells(Rows.Count, 1).End(xlUp).Row
With ComboBox2
If Not IsArrow Then .List = Worksheets("SHEET5").Range("A2").CurrentRegion.Offset(1, 1).Value
If .ListIndex = -1 And Len(.Text) Then
For i = .ListCount - 1 To 0 Step -1
If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
Next i
.DropDown
End If
If ComboBox2.ListCount = 0 Then
MsgBox "Item Does Not Exisits"
End If
End With
End Sub
Private Sub ComboBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
FullList = Worksheets("SHEET5").Range("A2").CurrentRegion.Offset(1, 1).Value
IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then ComboBox2.List = FullList
End Sub
Private Sub ComboBox2_Click()
Dim idx As Long
idx = ComboBox2.ListIndex
If idx <> -1 Then
ComboBox3.Text = Worksheets("SHEET5").Range("A" & idx + 2).Value
textBox5.Text = Worksheets("SHEET5").Range("E" & idx + 2).Value
textBox6.Text = Worksheets("SHEET5").Range("F" & idx + 2).Value
End If
End Sub
Now What i desire is that if same thing can be adapted in much better method with smooth flow in VB.NET will always be grateful to you.
I tired the Following code in VB.NET19 adapted from https://www.codeproject.com/Articles...oBox-in-VB-Net
somehow succeded to get the Range of Column B values in ComboBox2 but missed to get respective data of different columns into respective Textboxes of that particular row in respective textboxes
Q1. How to get respective data from ComboBox when Selectedchange_event triggered or When Clicked on the item of combobox into respective textboxes from Worksheet
Q2. Any beautiful ways for searching items in Combobox and hiliting the same while being searched
Code:
VB.NET19 CODE
Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class ufComboBoxes
Private Sub ufComboBoxes_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim path As String = "C:\"
Dim strFilename As String = "C:\Trial\Data.xlsx"
Dim appXL As New Microsoft.Office.Interop.Excel.Application()
Dim wbK As Excel.Workbook
Dim wksSht5 As Excel.Worksheet
Dim wksNo As Integer = 1
Dim RngCombo2Cde As Excel.Range
Dim Rngcombo2Arr As String
wbK = appXL.Workbooks.Open(strFilename)
wksSht5 = CType(appXL.Worksheets("SHEET5"), Excel.Worksheet)
RngCombo2Cde = DirectCast(wksSht5.Range("B2:B25"), Excel.Range)
For i As Integer = 1 To RngCombo2Cde.Rows.Count
Dim colCell As Excel.Range = DirectCast(RngCombo2Cde.Rows(i), Excel.Range)
Rngcombo2Arr &= DirectCast(colCell.Value.ToString, String) & ","
Next
Rngcombo2Arr = Rngcombo2Arr.Remove(Rngcombo2Arr.Length - 1, 1)
ComboBox2.Items.AddRange(Rngcombo2Arr.Split(","c))
RngCombo2Cde = Nothing
wksSht5 = Nothing
wbK = Nothing
appXL = Nothing
End Sub
Public Sub AutoCompleteCombo_KeyUp(ByVal cbo As ComboBox, ByVal e As KeyEventArgs)
Dim sTypedText As String
Dim iFoundIndex As Integer
Dim oFoundItem As Object
Dim sFoundText As String
Dim sAppendText As String
'Allow select keys without Autocompleting
Select Case e.KeyCode
Case Keys.Back, Keys.Left, Keys.Right, Keys.Up, Keys.Delete, Keys.Down
Return
End Select
'Get the Typed Text and Find it in the list
sTypedText = cbo.Text
iFoundIndex = cbo.FindString(sTypedText)
'If we found the Typed Text in the list then Autocomplete
If iFoundIndex >= 0 Then
'Get the Item from the list (Return Type depends if Datasource was bound
' or List Created)
oFoundItem = cbo.Items(iFoundIndex)
'Use the ListControl.GetItemText to resolve the Name in case the Combo
' was Data bound
sFoundText = cbo.GetItemText(oFoundItem)
'Append then found text to the typed text to preserve case
sAppendText = sFoundText.Substring(sTypedText.Length)
cbo.Text = sTypedText & sAppendText
'Select the Appended Text
cbo.SelectionStart = sTypedText.Length
cbo.SelectionLength = sAppendText.Length
End If
End Sub
Private Sub ComboBox2_Leave(sender As Object, e As EventArgs) Handles ComboBox2.Leave
Dim recRowView As DataRowView
'Dim recName As DB.tblNameRow
AutoCompleteCombo_Leave(ComboBox2)
'OPTIONAL: Now you can do some extra handling if you want
'Get the Selected Record from my Data Bound Combo (Return Type is DataRowView)
recRowView = ComboBox2.SelectedItem
If recRowView Is Nothing Then Exit Sub
'Display the Name Info (Row Type comes from my bound Dataset)
'recName = recRowView.Row
'lblAccountNum.Text = recName.AccountNum
'lblCompanyName.Text = recName.CompanyName
''''''How to get following values in textbox5 and textbox6 with recRowView
textBox5.Text = Worksheets("SHEET5").Range("E" & idx + 2).Value
textBox6.Text = Worksheets("SHEET5").Range("F" & idx + 2).Value
End Sub
Private Sub ComboBox2_KeyUp(sender As Object, e As KeyEventArgs) Handles ComboBox2.KeyUp
AutoCompleteCombo_KeyUp(ComboBox2, e)
End Sub
Public Sub AutoCompleteCombo_Leave(ByVal cbo As ComboBox)
Dim iFoundIndex As Integer
iFoundIndex = cbo.FindStringExact(cbo.Text)
cbo.SelectedIndex = iFoundIndex
End Sub
End Class
Thankx in advance
SamD
Thread 2: 892141 :
11
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
What I would do is one of two things, both of which involves a multi column datatable.
Code:
Dim dt As New System.Data.DataTable
'---------------------------------
dt.Columns.Add("ComboBox", GetType(String))
dt.Columns.Add("Cell", GetType(String))
A) I would go through a loop and add in all values from the Excel Document into the datatable, instead of recalling them constantly with Excel running in the background. If I need to dump the data back into Excel, open the document again and then dump the data. This will also clear the document up in case other people are going to be using it.
Code:
dt.Rows.Add(Nothing, Nothing) 'replace nothing with your values
In my thoughts, the datatable would populate column 1 (your combobox data) and column 2 (your textbox data). Once it is all loaded you can loop through it and add all the values to the combobox. When the combobox value is selected it will update the textbox with the second column data.
Code:
'for column one
For i As Integer = 0 To dt.Rows.Count - 1
With ComboBox1.Items
.Clear()
.Add(dt.Rows(i).Item(0))
End With
ComboBox1.SelectedIndex = 0
Next
Put this into your SelectionChangeEvent for the combobox
Code:
Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
B) Load the values on demand. I would only do this if you feel like your data in the the textbox will need to change or be updated more frequent than A would allow.
Using the same method above you create a loop to add in two columns into a datatable. The first column will be your combobox data and the second will be the row associated with that data (textbox data). When a combobox item is selected, open Excel and pull data from that row and use that for your textbox value.
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
And for your auto complete, it has been well documented here: Vb.net ComboBox Autocomplete. It is more or less some settings that you enabled :)
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Frabulator Really appreaciate your suggestions for Question1.
Although you have suggested the logic but i am unable to understand.
The syntaxes which you have shown where can i write them is it in Form_Load or any other sub-routine or whatever you have suggested Is there any other forum where it almost matches your logic. This has almost gone over my brains.
Can you show me a concrete example of what you are mentioning and deriving the desired results.
What prior syntaxes and Excel Workhseet references etc are required before
Code:
Dim dt As New System.Data.DataTable
'---------------------------------
dt.Columns.Add("ComboBox", GetType(String))
dt.Columns.Add("Cell", GetType(String))
https://www.dotnetperls.com/datatable-vbnet
The above link shows almost the same thing as you mentioned but not how it relates combobox but only adding data to Datatable.
If i get clarity for above then i can ask for below
Quote:
In my thoughts, the datatable would populate column 1 (your combobox data) and column 2 (your textbox data). Once it is all loaded you can loop through it and add all the values to the combobox. When the combobox value is selected it will update the textbox with the second column data.
Code:
'for column one
For i As Integer = 0 To dt.Rows.Count - 1
With ComboBox1.Items
.Clear()
.Add(dt.Rows(i).Item(0))
End With
ComboBox1.SelectedIndex = 0
Next
Thanks for suggestin the Link for Question2
SamD
Thread 2: 892141 :
12
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Hi
I tried as is from https://social.msdn.microsoft.com/Fo...orum=vbgeneral
Code:
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
ComboBox1.Items.Add(GetDataFromExcelByCom(False).Rows(i)(0))
Next
For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
ComboBox2.Items.Add(GetDataFromExcelByCom(False).Rows(i)(1))
Next
End Sub
:mad:
I get error on below line as System.NullReferenceException: 'Object variable or With block variable not set.'
This exception was originally thrown at this call stack:
For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
SamD
Thread 2: 892141 :
13
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Quote:
Originally Posted by
SamDsouza
Frabulator Really appreaciate your suggestions for Question1.
Although you have suggested the logic but i am unable to understand.
The syntaxes which you have shown where can i write them is it in Form_Load or any other sub-routine or whatever you have suggested Is there any other forum where it almost matches your logic. This has almost gone over my brains.
Can you show me a concrete example of what you are mentioning and deriving the desired results.
I admit, it is awesome that you have taken the insensitive to actually look up this information before commenting back (most people just assume someone on here will do the leg work for them). So thank you!
As far as getting the code in the exact place, I can try to guide you, but my setup might not be 'exactly' the same as yours.
------
Connecting to Excel
As I am sure that you have found out, you need to somehow connect to Excel. There are several methods of doing this: using the pre-built references with Visual Studio Professional ($$$), unpackaging the excel document and reading it as an XML file, or using an interop. I prefer the interop method because A) its free and B) while it can be a pain to setup to work, once it is set up, it works brilliantly.
From the code provided above, it looks like you have the interop already set up.
Code:
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
It is also important to know that the year of release for the interop will work for that version of excel onward. Interop Version 12 will work for Excel v12 (ie: Excel 2007). This version will work for 2007-2019/365 desktop. This version is also limited to what features were present in excel 2007. Interop Version 14 (ie: Excel 2010) will not work with Excel 2007, but will work with 2010-2019/365 desktop... and so on. Excel 2007 was the first version that came with the ribbon/toolbars that we have today and access to the expanded VBA editor. ALMOST everyone is using 2007+. I myself am still running 2010 on one machine and 2016 on my work station. This is something that you want to keep in mind when selecting your interop version.
Here is a link to all versions of Excel and their version numbers.
Next, you want to specify WHAT the excel app is, WHERE it is and HOW you are going to modify/use it.
This code is similar to the code you are using, but I am copying this from my own application, so the declared names may be different.
This can be declared as a public outside of the main sub or inside of a sub that pulls data. It is up to you.
Code:
Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is
To start gathering or creating data you need to either start a new workbook:
Code:
xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
xlWorkBook = xlApp.Workbooks.Add()
xlWorkSheet = CType(xlWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
or open an existing one:
Code:
Dim SheetNamePlease as String = "Sheet 1"
xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
xlWorkBook = xlApp.Open(strFilename)
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
If you would like to make your excel document run in the background and not show the user the Excel window, add the following codes after opening or creating a new document:
Code:
'make sure it is not visible
xlApp.Visible = False
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
To pull your data from Excel you are going to need to specify two things. A string for gathering the data, and a range from excel as the source. These can be accomplished as such:
Code:
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim str As String = "My Value"
To put data into a range in the Excel Document you need to first decide where the range you want to dump the data is at, and you need to decide what the data you are dumping is. In this example I am using the 'str' as the data dump and the cell 'E2' as the range in the per-determained Excel document.
Code:
Dim RowStart as Integer = 1 'you can use this in a loop to go through all cells in the column if needed
rng = xlWorkSheet.Range("E" & RowStart)
rng.Value = str
ReleaseCOM(rng)
To pull data from the Excel Document you do the same thing, except you are using a string to hold the data from the range. An example of this could be:
Code:
Dim RowStart as Integer = 1 'you can use this in a loop to go through all cells in the column if needed
rng = xlWorkSheet.Range("E" & RowStart)
str = Cstr(rng.Value) 'this should always be a string, but just in case something got a little screwy, we are going to convert to a string
ReleaseCOM(rng)
Once you are finished gathering and dumping data, if you want to show the Excel document, you can issue the following commands:
Code:
xlApp.ScreenUpdating = True
xlApp.Visible = True
xlApp.DisplayAlerts = True
xlApp.WindowState = XlWindowState.xlMaximized
If you are finished 100% and wish to close out of Excel, use the following codes:
Code:
Try
xlWorkBook.Close()
Catch ex As Exception
End Try
releaseObject(xlWorkSheet)
ReleaseCOM(xlWorkSheet)
releaseObject(xlWorkBook)
ReleaseCOM(xlWorkBook)
releaseObject(xlApp)
ReleaseCOM(xlApp)
You may have noticed in my example the 'ReleaseCOM()' and the 'releaseObject()' calls in my code. I found these little snipbits online many years ago and have adopted them into all of my Excel linked applications. These codes disconnects all of your references from Excel, Workbooks, Apps, Ranges, etc., in one simple solution. I would recommend you dumping these somewhere in your main class of your application and reference them when needed.
Code:
#Region "Excel Remover"
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
#End Region
------------------
Looping your data
As I have previously mentioned, you can loop through your Excel document to pull your data and populate it into a datatable.
Please note that this is an example and you will need to modify all of this code to fit your needs.
In your form_load sub (you can access this by double clicking in Visual Studio on the top portion of your form in display mode), you will want to run a sub class that pulls the data from excel. An example would be like this:
Code:
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access
Public Class Form1
Public dt As New System.Data.DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
PullExcel()
End Sub
Sub PullExcel()
'your code for excel
End Sub
End Class
It is a good idea to section these request off in separate subs just in case you ever need to run the sub again. It will prevent you from coping and pasting code (never a good practice).
Populating the datatable is easy enough to do, but you need the data first. You will need to locate/open the Excel Document and run the data through a loop.
Code:
Sub PullExcel()
dt.Clear() 'clears it out so we can start fresh
dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is
Dim strFilename As String = "C:\File.xlsx"
Dim SheetNamePlease as String = "Sheet 1"
xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
xlWorkBook = xlApp.Open(strFilename)
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim str1 As String = "My ComboBox"
Dim str2 As String = "My TextBox"
For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
Try
rng = xlWorkSheet.Range("E" & i)
str = Cstr(rng.Value)
If str = "" Then Exit For 'this closes out of the loop so we dont add empty points
'now we are going to do this again with str2 so we have a valid data to put into the textbox
ReleaseCOM(rng)
rng = xlWorkSheet.Range("F" & i)
str2 = Cstr(rng.Value)
ReleaseCOM(rng)
dt.Rows.Add(str1, str2) 'this adds in the data we gathered
Catch ex As Exception
Exit For
End Try
Next
Try
xlWorkBook.Close()
Catch ex As Exception
End Try
releaseObject(xlWorkSheet)
ReleaseCOM(xlWorkSheet)
releaseObject(xlWorkBook)
ReleaseCOM(xlWorkBook)
releaseObject(xlApp)
ReleaseCOM(xlApp)
End Sub
Once that is done we can make a call that will add the datatable values to the combobox. This is easily done like so:
Code:
Sub AddToCombobox()
With ComboBox1.Items
.Clear()
For i = 0 To dt.Rows.Count - 1
.Add(dt.Rows(i).Item(0))
Next
End With
End Sub
The ability to automatically update the textbox when a selection is made on the combobox can be activated by either double clicking on the combobox in the designer form, or going to the properties tab when the combobox is selected. The sub should be "SelectedIndexChanged":
Code:
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
Textbox1.Update 'Updates the textbox
End Sub
------
When all that is put together it should look something like this: [untested]
Code:
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Access
Public Class Form1
Public dt As New System.Data.DataTable
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
PullExcel()
AddToCombobox()
Me.Update
End Sub
Sub PullExcel()
dt.Clear() 'clears it out so we can start fresh
dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is
Dim strFilename As String = "C:\File.xlsx"
Dim SheetNamePlease as String = "Sheet 1"
xlApp = New Microsoft.Office.Interop.Excel.Application
xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
xlWorkBook = xlApp.Open(strFilename)
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim str1 As String = "My ComboBox"
Dim str2 As String = "My TextBox"
For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
Try
rng = xlWorkSheet.Range("E" & i)
str = Cstr(rng.Value)
If str = "" Then Exit For 'this closes out of the loop so we dont add empty points
'now we are going to do this again with str2 so we have a valid data to put into the textbox
ReleaseCOM(rng)
rng = xlWorkSheet.Range("F" & i)
str2 = Cstr(rng.Value)
ReleaseCOM(rng)
dt.Rows.Add(str1, str2) 'this adds in the data we gathered
Catch ex As Exception
Exit For
End Try
Next
Try
xlWorkBook.Close()
Catch ex As Exception
End Try
releaseObject(xlWorkSheet)
ReleaseCOM(xlWorkSheet)
releaseObject(xlWorkBook)
ReleaseCOM(xlWorkBook)
releaseObject(xlApp)
ReleaseCOM(xlApp)
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
Textbox1.Update 'Updates the textbox
End Sub
Sub AddToCombobox()
With ComboBox1.Items
.Clear()
For i = 0 To dt.Rows.Count - 1
.Add(dt.Rows(i).Item(0))
Next
End With
End Sub
#Region "Excel Remover"
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
#End Region
End Class
I hope that wasnt too over the top, but your question(s) were a little indepth in response.
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Fabrulator
Indeed Fantastic Informative presentation
Really Dont know how to Thank you for the information you have provided.
Let me work, check on the last part and will revert back
Thanks
SamD
Thread 2: 892141 :
14
1 Attachment(s)
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Hi Sam,
for filtering and searching Data I would use OLEDB, it's much less code and easy to adjust
here a small sample, this is what the sheet looks like
Attachment 181619
and here the code for testing
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
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Frabulator
Part 1 of the Question has been resolved and as mentioned i've worked on the last part of your coding and made changes as per my requirements.
One Importing thing which i observed was Let us not Dim XlApp because when typing xlApp in the progamming editor of .Net19
the following words (not familar with technical terms) are typed
XlApplicationInternational
XlApplyNamesOrder
So the above is Important Note for Excel Lovers from VB.Net 19 to Excel for the Members of this forum and Non-members who browse for solutions.
So here is the Final refinement of the Code
Code:
Imports System
Imports System.IO
Imports System.Data
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Public Class ufComboBoxes4
Public dt As New System.Data.DataTable
Private Sub ufComboBoxes4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
PullExcel()
AddToCombobox()
Me.Update()
End Sub
Sub PullExcel()
dt.Clear() 'clears it out so we can start fresh
dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
Dim myExcel As New Microsoft.Office.Interop.Excel.Application()
Dim xlWrkBks As Excel.Workbooks = Nothing
Dim xlWorkbook As Excel.Workbook = Nothing
Dim xlWorksheet As Worksheet = Nothing
Dim xlWorksheets As Worksheets = Nothing
myExcel = New Microsoft.Office.Interop.Excel.Application
Dim strFilename As String = "C:\File.xlsx"
xlWorkbook = myExcel.Workbooks.Open(strFilename)
Dim SheetNamePlease As String = "Sheet 1"
xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim str1 As String = "My ComboBox"
Dim str2 As String = "My TextBox"
Str1 = CStr(rng.Value) 'this should always be a string, but just in case something got a little screwy, we are going to convert to a string
ReleaseCOM(rng)
For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
Try
rng = xlWorkSheet.Range("B" & i)
str1 = CStr(rng.Value)
If str1() = "" Then Exit For
'now we are going to do this again with str2 so we have a valid data to put into the textbox
ReleaseCOM(rng)
rng = xlWorkSheet.Range("C" & i)
str2 = CStr(rng.Value)
ReleaseCOM(rng)
dt.Rows.Add(str1, str2) 'this adds in the data we gathered
Catch ex As Exception
Exit For
End Try
Next
Try
xlWorkBook.Close()
Catch ex As Exception
End Try
releaseObject(xlWorkSheet)
ReleaseCOM(xlWorkSheet)
releaseObject(xlWorkBook)
ReleaseCOM(xlWorkBook)
releaseObject(xlApp)
ReleaseCOM(xlApp)
End Sub
Sub AddToCombobox()
With ComboBox1.Items
.Clear()
For i = 0 To dt.Rows.Count - 1
.Add(dt.Rows(i).Item(0))
Next
End With
End Sub
Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
Textbox1.Update 'Updates the textbox
End Sub
'#Region "Excel Remover"
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
End Class
Indeed without your informative guidance i would have gone crazy and would have been still struggling.
Your Valuable inputs were truly helpful to me to achieve the desired result. Thanks a Tonne. :)
ChrisE
I will try yours give me sometime to revertback.
SamD
Thread 2: 892141 :
15
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Fabrulator I forgot to ask What is GC
in
Code:
If GCCollect Then
GC.WaitForPendingFinalizers()
GC.Collect()
And how about adding one more textbox and getting respective data of another column related to selected item of combobox
SamD
Thread 2: 892141 :
16
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Quote:
Originally Posted by
SamDsouza
Fabrulator I forgot to ask What is GC
in
Code:
If GCCollect Then
GC.WaitForPendingFinalizers()
GC.Collect()
SamD
Thread 2: 892141 :
16
GC = Garbage Collector
You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
.Paul.
Quote:
GC = Garbage Collector
You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.
OK
One should know
Thanks for the information
Pl ignore the below as in #10 as i have resolved the same too
Quote:
And how about adding one more textbox and getting respective data of another column related to selected item of combobox
Thanks
SamD
Thread 2: 892141 :
17
Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question
Quote:
Originally Posted by
ChrisE
Hi Sam,
for filtering and searching Data I would use OLEDB, it's much less code and easy to adjust
here a small sample, this is what the sheet looks like
Attachment 181619
and here the code for testing
Thank you! I will have to look into this for myself :)
Quote:
Originally Posted by
.paul.
GC = Garbage Collector
You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.
Thank you for responding back in my absence :)!
Quote:
Originally Posted by
SamDsouza
.Paul.
OK
One should know
Thanks for the information
If your questions are resolved could you mark the thread as resolved? This can be done in the top right corner of the first post in the dropdown menus.
This helps people who are looking for the same solution know that the issue was resolved.
It also helps to rate the post that helped.
Thanks! Good luck with your program :)
