Would like to convert Few VBA-Excel subroutines to VB.Net19
Hello
Basically i wanted to convert the following VBA-Excel codes to VB.Net19
The object in VBA excel is Listbox and unfortunately there is no listview Object in VBA Excel
As i got stuck For viewing Each Group/Item(s) with its Selected Items/Subitems in Listview
VBA-Excel Code
Code:
Option Explicit
Dim lngcount As Long, iCount As Long, CurRec As Long, j As Long
Dim Recordset As Boolean
Dim myList As New Collection
Private Sub UserForm_Initialize()
Dim myarray
Dim lstRow As Long
With listBox1
.ColumnCount = 3
.ListStyle = fmListStyleOption
.MultiSelect = fmMultiSelectMulti
.ColumnWidths = "55,70,80"
lstRow = Worksheets("Sheet2").Cells(Rows.Count, 2).End(xlUp).Row
myarray = Worksheets("Sheet2").Range("B3:D" & lstRow).Value
.List = myarray
End With
CurRec = 1
End Sub
Private Sub cmbGroup_Change()
Dim myarray
Dim lstRow As Long
Recordset = False
Dim myRanges As Range
Set myRanges = Sheets("Sheet2").Range("B3:D10")
Dim idx As Long
idx = cmbGroup.ListIndex
If idx <> -1 Then
txtGroup.Text = Worksheets("Sheet1").Range("B" & idx + 3).Value
End If
End Sub
Private Sub txtGroup_Change()
Dim sCount As Long, lstRow As Long
Dim rngSource
Dim mySelections
Dim i As Long, j As Long
Recordset = False
With Sheets("Sheet2") 'Sheets("Sheet1")
.Activate
'lstRow = .Cells(Rows.Count, 1).End(xlUp).Row
lstRow = .Cells(Rows.Count, 2).End(xlUp).Row
rngSource = .Range("B3:D" & lstRow).Value
End With
If Not txtTrial.Text = "" Then
For sCount = listBox1.ListCount - 1 To 0 Step -1
If InStr(1, LCase(listBox1.List(sCount, 0)), LCase(txtTrial.Text)) = 0 Then
listBox1.RemoveItem sCount
End If
Next sCount
Else
listBox1.List = rngSource
End If
End Sub
Private Sub cmdSelectAdd_Click()
AppendRec True
End Sub
Sub AppendRec(sAdd As Boolean)
Dim myItem As Collection, blnSelected As Boolean ',icount As Long,
Dim i As Long
Dim noneSelectCount As Integer
blnSelected = False
Set myItem = New Collection
noneSelectCount = listBox1.ListCount 'newy entered
lngcount = 0
For iCount = 0 To listBox1.ListCount - 1
If listBox1.Selected(iCount) Then
blnSelected = True
myItem.Add Array(listBox1.List(iCount, 0), listBox1.List(iCount, 1), listBox1.List(iCount, 2))
lngcount = lngcount + 1
End If
Next iCount
Select Case sAdd
Case True
If blnSelected Then
myList.Add myItem
Else
myItem.Add Array(listBox1.List(0, 0), listBox1.List(0, 1), listBox1.List(0, 2))
myList.Add myItem
lngcount = lngcount + 1
End If
List_Current_Display (CurRec)
End Select
End Sub
Public Sub List_Current_Display(selItemsCount As Long)
Dim checkItem As Long, intItem As Long
Dim Newarray()
Dim myItem As Collection
Recordset = True
listBox1.Clear
On Local Error GoTo errlcl
ReDim Preserve Newarray(1 To myList(selItemsCount).Count, 1 To 3)
For checkItem = 1 To myList(selItemsCount).Count
For intItem = 1 To 3 '
Newarray(checkItem, intItem) = myList(selItemsCount).Item(checkItem)(intItem - 1)
Next
Next
listBox1.List = Newarray
errlcl: Resume Next
End Sub
Private Sub cmndNext_Click()
If CurRec < 20 Then
CurRec = CurRec + 1
List_Current_Display (CurRec)
End If
End Sub
Well the below is the brief Explanation of above subroutines of VBA-Excel
1. Publicly defined variables with Option Explicit
2. Uf_Initiliaze - Form being Loaded with Listbox
3. ComboBox as cmbGroup to Select Group which displays its respective items in Listbox with help of TextBox_Change Event ie txtGrop_Change
4. cmdSelectAdd_Click -> Command button to Select items calling subroutine AppendRec(True) where Each Group Entered with SELECTED List of Respective Group Items
so basically For Eg. Group Selected is "Things" its Selected Items/Subitems could be Laptop Mobiles and Headphones out of the "Things"Full List
Group Selected is "Fruits" its Selected Items/Subitems could be Banana and kiwi out of the "Fruits" Group List
5. List_Current_Display(curRec) -> to display the Selected items Preserved in Array of Above Gropups "Things", "Fruits" etc ie to display Selected List of Each Group
6. cmndNext_Click -> Command button to Display the next Current(Record) of Group with its Selected List of Items
The below displays the Selected for One Group only as per thread 892479
What if i had to incorporate the above VBA AppendRec(True) Subrotine in below Sub-Routine of VB.Net19. So how do i go about it ?
VB.19 Code for SelectedItems for a Single Group
Code:
Private Sub CmdAddSelectedItems_Click(sender As Object, e As EventArgs) Handles CmdAddSelectedItems.Click
Dim items as New List(of ListViewItem)
For selItemsCount As Integer = 0 To ListView1.Items.Count - 1
If ListView1.Items(selItemsCount).Checked = True Then
items.Add(New ListViewItem(ListView1.Items(selItemsCount).SubItems().Cast(Of ListViewItem.ListViewSubItem).Select(Function(s) s.Text).ToArray))
End If
Next
ListView1.Items.Clear
ListView1.Items.AddRange(items.ToArray)
End Sub
If i could be assisted in converting Excel VBA above Coding specially List_current_Display(curRec) and AppendRec(true) to VB.Net 19.
A straight forward request : Don't allow me to post replies for the Syntax Errors
Thanks
SamD
Thread 11 892555
57
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
In short you're asking others to rewrite your vba code in vb.net? Good luck, unless you have any specific questions.
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Peter Swinkels
Quote:
In short you're asking others to rewrite your vba code in vb.net? Good luck, unless you have any specific questions.
Specifically i've asked for the help of Coverting only two sub-routines.
For me to send the VBA code was to see the effect on .xlsm macro enabled XL-file and come up with similar on vb.net
Frankly speaking I Got stuck at the incorporation of Arrays with VS-2019 syntaxes
SamD
Thread 11 892555
58
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Peter Swinkels
Thanks for the reference. Let me give it a try and revert back.
By the way where does one see the result of Console.Writeline and Console.Readkey ?
SamD
Thread 11 892555
59
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
There should be an output window in your IDE.
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Quote:
Let me give it a try and revert back.
Unfortunately i am completely stuck. Although i went through the above Link for basic Array Examples.
SamD
Thread 11 892555
60
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
I'm not familiar with how listboxes work in Excel. It's clearly different from what they do in .NET. It looks like they are more of a datatable in that they can have multiple columns. That makes me think there isn't a direct conversion of the AppendRec method. Instead, there's more like an equivalent conversion.
What is myItem in that VBA code? A collection might look like a list, but in this case, it seems that what is in each slot is a somewhat more complicated object. Not being familiar with some of the methods used, and not being willing to look them up, it appears that what ends up in myItem is essentially a table with two columns and some number of rows, though it also appears that the first column may be nothing at all, so perhaps it's just a series of items.
Then there's myList, which is another collection. Does it hold multiple collections? To what end? A List(of List(of T)) seems like what myList is, but using such a thing is rarely the right thing to do.
I can see what the code is doing, but I'm not clear what is in myItem, as it is dependent on the layout of that multi-column listbox. It holds some number of triplets of values, and perhaps that's all. Triplets of values sounds like a class, to me. Is there some reason why they shouldn't be? If they can be, then myItem would just be a List of that class.
1 Attachment(s)
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
@Sam
why does it have to be a Listview?
As I have shown you before the way to go is to use 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
sample Excelfile
Attachment 181854
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Shaggy Hiker Sir,
Quote:
I'm not familiar with how listboxes work in Excel. It's clearly different from what they do in .NET. It looks like they are more of a datatable in that they can have multiple columns.
Listbox and many other common objects in VBA are having limited features. and are not the similar as in VB6 upto vb.net19
One creates a Listbox as Datatable by adding columns if not mistaken not more than 10 columns are created in Listbox.
Quote:
That makes me think there isn't a direct conversion of the AppendRec method. Instead, there's more like an equivalent conversion
AppendRec Sub-rotuine was to Create Array of Arrays and was to save the array of Each Group Selected Items Displayed in Listbox1
Quote:
What is myItem in that VBA code? A collection might look like a list, but in this case, it seems that what is in each slot is a somewhat more complicated object.
Sir, Please Relax. It is not all complicated if you just copy the VBA code in your Xl-file Create the .xlsm File by adding the userform, Combobox, Listbox and 2 command Buttons. ie Save Button and Next button
and Sheet1 with data as per the mentioned range in #1
I strongly feel that for this case please create above .xlsm file and get a feel of VBA-Excel Enviornment. Many Doubts would be cleared on the questions you have asked and the results i want to achieve.
Quote:
it is just that VBA-Excel Simple Userform object Listbox has only given a simple challenge to VB.Net 19's quite sophisticated Listview object
ie VBA's Listbox asking to vb.net19s Listview
Sir, Can you hold the records of my Friend combobox Clicked: Goup value and with my Selected stored Items as per my style and Function
:)
myList and myItem are defined as New Collection.
Probably you are getting confused between Item of Listview and Defined myItem of Listbox
Chris dear
Both of us are in Fencing mode. You pushing me towards OLED and DGV and me defending myself for not shifting to OLED and DGV. Boss, I've not used any time of OLED. This will take time for me to understand. Please forgive me at present and just for time being. I shall personally message you when I will begin a project using DGV with OLED....FYI Even i've not used MS-Access for anytype of working.
SamD
Thread 11 892555
61
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Quote:
Originally Posted by
SamDsouza
Chris dear
Both of us are in Fencing mode. You pushing me towards OLED and DGV and me defending myself for not shifting to OLED and DGV. Boss, I've not used any time of OLED. This will take time for me to understand. Please forgive me at present and just for time being. I shall personally message you when I will begin a project using DGV with OLED....FYI Even i've not used MS-Access for anytype of working.
SamD
Thread 11 892555
61
OK
good luck
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
I think that's my response, as well. You want to migrate from VBA, but only by using VBA-like parts in VB.NET. There are not all that many of those, and they don't always work very well. Why bother moving from VBA in the first place if you don't want to move from VBA?
On the other hand, I was amused that you suggest that I set up a VBA project to learn that stuff, and in the same post said you weren't interested in learning ADO.NET and the use of one particular control, both of which would do what you want more easily than the contorted fashion that you are attempting.
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Sir,
Quote:
I think that's my response, as well. You want to migrate from VBA, but only by using VBA-like parts in VB.NET. There are not all that many of those, and they don't always work very well. Why bother moving from VBA in the first place if you don't want to move from VBA?
Infact i am really eager to move from VBA to VB.net19.
Quote:
On the other hand, I was amused that you suggest that I set up a VBA project to learn that stuff, and in the same post said you weren't interested in learning ADO.NET and the use of one particular control, both of which would do what you want more easily than the contorted fashion that you are attempting.
My purpose here was to see the effects on ListBox of Userform with VBA coding and if the same thing could be achieved through Listview Object of VB.Net
Fortunately or Unfortunately i started this project with Keeping in my mind with Listview object. I found Listview object much much better with very good options of its properties.
I was and still asking for help.
SamD
Thread 11 892555
62
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
ChrisE
As per your post #9 Can i use Listview1 instead of DGV ?
if yes, Can the Listview work as per the VBA coding of .XLSM File and the XL-Template execution
Started this project with Listview because never explored on DGV. The reason if i re-start the project with DGV then similar type of threads could be raised for basics of DGV(not sure as of now) as i raised few threads in this forum for Listview.
To replica this project in VB.net was to have altogether different Feel with beautfiul features against the very limited features of VBA-Excel.
Lastly
can we REDIM PRESERVE the below syntaxes marked in Red Following as per #1
if yes then How ?
Code:
Private Sub CmdAddSelectedItems_Click(sender As Object, e As EventArgs) Handles CmdAddSelectedItems.Click
Dim items as New List(of ListViewItem)
For selItemsCount As Integer = 0 To ListView1.Items.Count - 1
If ListView1.Items(selItemsCount).Checked = True Then
items.Add(New ListViewItem(ListView1.Items(selItemsCount).SubItems().Cast(Of ListViewItem.ListViewSubItem).Select(Function(s) s.Text).ToArray))
End If
Next
ListView1.Items.Clear
ListView1.Items.AddRange(items.ToArray)
End Sub
Unfortunately going Crazy :mad:
SamD
Thread 11 892555
63
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
The DGV is ideal for displaying data. The ListView is not as versatile. You’re barking up the wrong tree…
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
@Sam
like Paul said, the DGV is the better option.
what are you filling anyway from the Excel sheet?
Code:
Private Sub CmdAddSelectedItems_Click(sender As Object, e As EventArgs) Handles CmdAddSelectedItems.Click
Dim items as New List(of ListViewItem)
For selItemsCount As Integer = 0 To ListView1.Items.Count - 1
If ListView1.Items(selItemsCount).Checked = True Then
items.Add(New ListViewItem(ListView1.Items(selItemsCount).SubItems().Cast(Of ListViewItem.ListViewSubItem).Select(Function(s) s.Text).ToArray))
End If
Next
ListView1.Items.Clear
ListView1.Items.AddRange(items.ToArray)
End Sub
you say..
Quote:
Infact i am really eager to move from VBA to VB.net19.
perhaps it is a good time to step away from Excel and use a Database?
it seems you want to use Excel as a Database which isn't really a good idea.
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Quote:
.Paul
The DGV is ideal for displaying data. The ListView is not as versatile. You’re barking up the wrong tree…
OK
Quote:
perhaps it is a good time to step away from Excel and use a Database?
it seems you want to use Excel as a Database which isn't really a good idea.
Fortunately or unfortunately I've not used SQL nor MS-Access. I've just opened MS-Access on 1 or 2 times but did not explore the same. Not even data entered in Access
I don't know even how SQL looks like. Most of the time Results derived was out of VBA.
Code:
Products Name Qty
Fruits Apple 20
Fruits Banana 40
Fruits Kiwi 60
Fruits Oranges 30
Fruits Mangoes 40
Things Laptop 12
Things Mobile 100
Things HeadPhones 23
for the above Combobox displays the Following Products
clicking on the Products in VBA Displays the List with Fruits and Lists and the LISTBOX respective items of Fruits if selected or Things if selected
LISTBOX displays all the items wrt fruits or things
then selection is made either all selected or 2 or 1 and ADD them to array as per AppendRec
for eg
I select for Fruits First all Items are displayed
then from Fruits I select Apple and Kiwi Select, Add and Store in Array
Then from Things I select Mobile and Headphones Select, Add and Store in Array
By clicking Next button
Listbox Will display items as 1st Record of Fruits with ONLY Apple and Kiwi
then 2nd record of Things shall only display Mobile and Headphones
With above logic wanted to transfer to Listview.
So wanted to know NOW with DGV will it be possible to have the same effect. if yes then how?
SamD
Thread 11 892555
64
1 Attachment(s)
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Quote:
Fortunately or unfortunately I've not used SQL nor MS-Access. I've just opened MS-Access on 1 or 2 times but did not explore the same. Not even data entered in Access
I don't know even how SQL looks like. Most of the time Results derived was out of VBA.
this is a good place to start then learning SQL....
https://www.w3schools.com/sql/
here a sample with Image, I created a new Sheet for this named Sheet2
Code:
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
''Fill the ComboBox with Product names
ComboBox1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT Products,[Name] FROM [Sheet2$A:B] Where Products='Fruits'")
ComboBox1.DisplayMember = "Name"
End Sub
Attachment 181905
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
ChrisE
I get the following
The'microsoft.ACE.OLEDB.12.0' provider is not registered on local Machine
Also tried with OLEDB 15 get below error
The'microsoft.ACE.OLEDB.15.0' provider is not registered on local Machine
FYI i RIGHT-CLICKED on Solution Explorer ----> Configuration Manager--------> Selected x86 on On Active Selection platform
but could not succeed
Where i can get to download the driver
Using Windows 10 MS-Excel 2013 32 Bit
to avoid the following errors
The'microsoft.ACE.OLEDB.12.0' provider is not registered on local Machine
or
The'microsoft.ACE.OLEDB.12.0' provider is not registered on local Machine
SamD
Thread 11 892555
65
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
@Sam
well I am not here to Breastfeed, did you lookup what that means
Quote:
The'microsoft.ACE.OLEDB.12.0' provider is not registered on local Machine
it would be like me asking....where can I buy the best crossiants in Paris...
I'll let Sam look or just wait for somebody else looking
and you have to decide which way you want to proceed..
a) continue with Excel
b) start using a Database..(Access, SQL_Server...etc....)
Re: Would like to convert Few VBA-Excel subroutines to VB.Net19
Friends
i dont know how am i posting the current msg as i've got severe pain on my left shoulder and arm : cervical slipdisk and left fingers r numb
currently on complete bed rest.
SamD
66