-
Aug 30th, 2014, 03:36 AM
#1
Thread Starter
Member
[RESOLVED] From Excel To Listview
Dear All,
I am using Visual Basic 6 (SP6) with MySQL as database and would like to import multiple excel files into one listview in order to generate a comparative statement like view for each item/product quoted by various suppliers.
While, I have managed to populate the listview with the excel file, I am having trouble adding the content of the 2nd excel (and there onwards) to the existing layout. My listiview should look somewhat like this:
Item/Product Qty. Supplier1Code Rate AvailableQty Supplier2Code Rate AvailableQty
Product1 10 S0001 50 100 S0002 65 5
Product2 10 S0001 75 10 S0002 72 50
Product3 10 S0001 90 500 S0002 95 25
My code works for the first supplier but from the 2nd supplier onwards i am unable to set the item (Set L = Listview1.Listiems) which will enable me to populate the data for the 2nd supplier onwards.
Any hint/tips would be greatly appreciated.
Thanks
Tom
-
Aug 30th, 2014, 09:41 AM
#2
Hyperactive Member
Re: From Excel To Listview
There might b some problem with your code. Post the code!!!
-
Aug 30th, 2014, 10:00 AM
#3
Re: From Excel To Listview
Without seeing some code there is not much to be said other than make sure you are not clearing the listview between items and make sure that you actually ADD each item as you go.
On second look I see you have
Code:
Set L = Listview1.Listiems
Not sure what that would be in context of the other code but when adding a list item you would use ListItem rather than ListItems
Last edited by DataMiser; Aug 30th, 2014 at 10:09 AM.
-
Aug 30th, 2014, 10:25 AM
#4
Thread Starter
Member
Re: From Excel To Listview
Originally Posted by hamza.saleem
There might b some problem with your code. Post the code!!!
Set Listview columns:
Private Sub ListComparativeStatementListViewColumns()
ListView1.FullRowSelect = True
ListView1.View = lvwReport
ListView1.Gridlines = True
ListView1.ColumnHeaders.Clear
ListView1.ListItems.Clear
ListView1.FlatScrollBar = False
With ListView1.ColumnHeaders
.Add , , "RFQ No.", 900
.Add , , "TPL Part No.", 1900
.Add , , "OEM Part No.", 1900
.Add , , "R. Qty.", 850, lvwColumnRight
End With
End Sub
Populate The Listview with the First Excel File:
ListView1.ColumnHeaders.Add , , "Supplier", 900
ListView1.ColumnHeaders.Add , , "Unit Rate", 1400, lvwColumnRight
ListView1.ColumnHeaders.Add , , "A. Qty.", 850, lvwColumnRight
Dim l As ListItem
For intRFQItemsCount = 1 To lvwTPL_RFQItems.ListItems.Count
strRFQ_TPLPartNo = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(1)
For intSupplierQuoteItemsCount = 1 To lvwSupplierRates.ListItems.Count
strSupplierQuote_TPLPartNo = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
If strSupplierQuote_TPLPartNo = strRFQ_TPLPartNo Then
Set l = ListView1.ListItems.Add(, , lblRFQNumber.Caption)
l.SubItems(intFirstCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(1)
l.SubItems(intSecondCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
l.SubItems(intThirdCol) = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(2)
l.SubItems(intFourthCol) = lblSupplierCode.Caption
l.SubItems(intFifthCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(3)
l.SubItems(intSixthCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(4)
End If
Next
Next intRFQItemsCount
Populate the same Listview the 2nd time onwards:
ListView1.ColumnHeaders.Add , , "Supplier", 900
ListView1.ColumnHeaders.Add , , "Unit Rate", 1400, lvwColumnRight
ListView1.ColumnHeaders.Add , , "A. Qty.", 850, lvwColumnRight
ListView1.ColumnHeaders.Add , , "TPL Part No.", 1900
Dim l As ListItem
For intRFQItemsCount = 1 To lvwTPL_RFQItems.ListItems.Count
strRFQ_TPLPartNo = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(1)
For intSupplierQuoteItemsCount = 1 To lvwSupplierRates.ListItems.Count
strSupplierQuote_TPLPartNo = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
If strSupplierQuote_TPLPartNo = strRFQ_TPLPartNo Then
Set l = ListView1.ListItems(ListView1.SelectedItem.Index)
' l.SubItems(7) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(1)
' l.SubItems(8) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
' l.SubItems(10) = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(2)
l.SubItems(7) = lblSupplierCode.Caption
l.SubItems(8) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(3)
l.SubItems(9) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(4)
l.SubItems(10) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
Exit For
End If
Next
Next intRFQItemsCount
-
Aug 30th, 2014, 10:38 AM
#5
Thread Starter
Member
Re: From Excel To Listview
Hi Data,
Thanks, but which line are you referring to?
-
Aug 30th, 2014, 11:21 AM
#6
Re: From Excel To Listview
Your code at the bottom would be simply changing the values of the selected item in the listview. Nothing is being added.
You only need to add the headers once
-
Aug 30th, 2014, 11:27 AM
#7
Thread Starter
Member
Re: From Excel To Listview
Hi Data,
The headers are included in this loop coz each time a new excel file is loaded the headers will be added at the end with the relevant data from that excel file.
-
Aug 30th, 2014, 11:57 AM
#8
Re: From Excel To Listview
The point is that you already have headers once you add them the first time. You can change the text on them if needed. As coded I would expect that after that second set of add headers you would have
"Supplier" "Unit Rate""A. Qty." "Supplier" "Unit Rate" "A. Qty.""TPL Part No." as your column headers because you are adding them all again rather than adding just the one that is new.
Your code in the first part adds items to the listview using this line
Code:
Set l = ListView1.ListItems.Add(, , lblRFQNumber.Caption)
Your second piece however does not do that it instead modifies the selected item, assuming of course an item is selected.
Code:
Set l = ListView1.ListItems(ListView1.SelectedItem.Index)
So if there were 10 items that should be added there what would actually happen is it would replace the text on the selected item 10 times with only the last one showing.
If you want the contents of both files in the listview then the second piece of code needs to add items rather than work with the selected item.
If you want only one file showing at a time then you would clear the listitems and then add items rather than what you are doing now.
If you are trying to do something else then you should try to give us a clear description of what you are trying to do and what results you are getting.
-
Aug 30th, 2014, 12:05 PM
#9
Thread Starter
Member
Re: From Excel To Listview
Thanks Data,
I realized that in the 2nd code, i am merely replacing and not adding. However, if i use Set l = ListView1.ListItems.Add(, , lblRFQNumber.Caption) it is adding the content of the 2nd excel file at the bottom and not veritically adjacent to the first set of information. The objective is to display data from all suppliers next to each other to see who has quoted what for each item.
Anyway, the output i want is already included. I paste it again. I want the content of the first excel file to be there and then add the content of the 2nd excel file and so on.
My final output should be something like this:
Item/Product Qty. Supplier1Code Rate AvailableQty Supplier2Code Rate AvailableQty
Product1 10 S0001 50 100 S0002 65 5
Product2 10 S0001 75 10 S0002 72 50
Product3 10 S0001 90 500 S0002 95 25
Last edited by tomahawk; Aug 30th, 2014 at 12:11 PM.
-
Aug 30th, 2014, 12:51 PM
#10
Re: From Excel To Listview
So what you are saying is that you would like to add the data from the second file on the same rows but in new columns?
If that be the case then the first thing you need to consider is that if the second file has more rows than the first then there would not be enough rows to place the data.
As for actually placing the data you should either not be using the selected item [you could use listitems(index)] or you should be changing the selected item through code.
Your code is very hard to read without formatting and those long variable names but I do not see where you are reading anything from a file, instead it looks like you are adding items from another listview.
How would you know from that code which row the data should be placed into?
-
Aug 30th, 2014, 01:18 PM
#11
Thread Starter
Member
Re: From Excel To Listview
Thanks again, Data
The attached screenshot should help in understanding. Yes, I am loading the supplier's excel file into a listview(the second one in the screenshot). The third listview is the final output required. I have presaved the location of the excel files in two labels in the right top side. The button next to the excel files loads the data into the supplier's listview. The first listview contains the items i need to match and transfer to the third listview. So for each item from the 1st listview, i need to check matching record from the suppliers quote and populate the suppliercode (from the suppliercode label at the top), the rate and the qty. available for the same item. It is less likely the supplier will quote for items not required (the supplier's file will be sent to them in which they will only indicate the rate and qty. available). However, the supplier might not necessarily have the items needed in which case they will leave it blank or indicate 0 availability. I do understand the concept is rather complicated. The first suppliers data (duly matched with the required items) is already shown in the screenshot. The last column for the first supplier is the A.Qty. The second supplier's data (SupplierCode, Rate and Available Qty) should start on the matched rows after last A.Qty column and so on.
The first big command button (command1) loads the items asked for and the first suppliers rates etc. into the 3rd listview. The second big command button (command2) is used to execute the code for loading the data into the 3rd listview for the 2nd supplier and so on
But this is just for getting the code right since i am still experimenting. Once I get the code right, the command buttons etc. will be merged accordingly. I have used long variable names for better understanding only. Command2 button calls the following code to load the data for the first supplier
Private Sub Fill_ComparativeStatementListView()
ListView1.ColumnHeaders.Add , , "Supplier", 900
ListView1.ColumnHeaders.Add , , "Unit Rate", 1400, lvwColumnRight
ListView1.ColumnHeaders.Add , , "A. Qty.", 850, lvwColumnRight
Dim l As ListItem
For intRFQItemsCount = 1 To lvwTPL_RFQItems.ListItems.Count
strRFQ_TPLPartNo = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(1)
For intSupplierQuoteItemsCount = 1 To lvwSupplierRates.ListItems.Count
strSupplierQuote_TPLPartNo = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
If strSupplierQuote_TPLPartNo = strRFQ_TPLPartNo Then
Set l = ListView1.ListItems.Add(, , lblRFQNumber.Caption)
l.SubItems(intFirstCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(1)
l.SubItems(intSecondCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(2)
l.SubItems(intThirdCol) = lvwTPL_RFQItems.ListItems(intRFQItemsCount).SubItems(2)
l.SubItems(intFourthCol) = lblSupplierCode.Caption
l.SubItems(intFifthCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(3)
l.SubItems(intSixthCol) = lvwSupplierRates.ListItems(intSupplierQuoteItemsCount).SubItems(4)
End If
Next
Next intRFQItemsCount
End Sub
-
Aug 30th, 2014, 01:33 PM
#12
Re: From Excel To Listview
Try changing this
Code:
Set l = ListView1.ListItems(ListView1.SelectedItem.Index)
To
Code:
Set l = ListView1.ListItems(intRFQItemsCount)
and see what happens
Also you should use code tags when posting code to retain formatting and make it easier to read
[code]Some Code
more code[/code]
Will give you
Code:
Some Code
more code
It will also retain spaces and indents
-
Aug 30th, 2014, 11:33 PM
#13
Thread Starter
Member
Re: From Excel To Listview
Thanks, but i got an 'Index Out Of Bounds' error on the same line - Set l = ListView1.ListItems(intRFQItemsCount). However it did populate the records in the last 3 columns.
-
Aug 31st, 2014, 01:24 AM
#14
Re: From Excel To Listview
When you get the error on that line hover your mouse over the intRFQItemsCount part and see what the value is then compare that with the actual number of items in the listview1
-
Sep 1st, 2014, 09:38 AM
#15
Thread Starter
Member
Re: From Excel To Listview
It is less than the count of RFQItems (20) since the supplier has only 18 matching records but 20 records in the file. So, i will have to figure out a way to load all 20 records from the suppliers file and show blank or non-matched info.
Last edited by tomahawk; Sep 1st, 2014 at 10:09 AM.
-
Sep 1st, 2014, 12:03 PM
#16
Re: From Excel To Listview
How about using the ListView1 for the look up and listview1 item count for the loop var. That way you are only looking up the items that are actual in listview 1 and not exceeding the count of items in listview 1
-
Sep 3rd, 2014, 03:58 AM
#17
Thread Starter
Member
Re: From Excel To Listview
Thanks Data,
Will try that and post the results.
-
Sep 3rd, 2014, 08:13 AM
#18
Re: From Excel To Listview
Originally Posted by tomahawk
My final output should be something like this:
Item/Product Qty. Supplier1Code Rate AvailableQty Supplier2Code Rate AvailableQty
Product1 10 S0001 50 100 S0002 65 5
Product2 10 S0001 75 10 S0002 72 50
Product3 10 S0001 90 500 S0002 95 25
Wouldn't it be easier to compare values it looked more like this?
Code:
Item/Product Qty SupplierCode Rate AvailableQty
Product1 10 S0001 50 100
Product1 10 S0002 65 5
Product2 10 S0001 75 10
Product2 10 S0002 72 50
Product3 10 S0001 90 500
Product3 10 S0002 95 25
-
Sep 12th, 2014, 12:30 AM
#19
Thread Starter
Member
Re: From Excel To Listview
Hi Logo,
Thanks but there can be as many as 300 items in the RFQ itself, which will mean too many rows once every supplier's excel file has been imported. The method used above will have at the most 300 items but only 3 columns extra per supplier.
I still welcome the suggestion as it might work for lesser rows.
Thanks
-
Sep 12th, 2014, 03:20 AM
#20
Re: From Excel To Listview
I would be more concerned about having too many columns than too many rows. Judging from your screenshot, you would only be able to view the information for 3 or 4 suppliers without scrolling. This makes it rather difficult to actually compare the info (visually, that is) for 5 or more suppliers - you simply could not view the first and fifth suppliers at the same time. Contrary to this, if you display them as I suggested, you could select a part number and see all info for 20+ suppliers of that part without scrolling.
-
Sep 12th, 2014, 11:11 AM
#21
Thread Starter
Member
Re: From Excel To Listview
I agree with you Logo. But it is known (in this particular case) that there will be no more than 5 suppliers. So I might be able to display info for all of the 5 suppliers. Besides, the client needs it that way. I myself would have opted for your suggestion since it would be more easier to save the data in a table (if need be). Re-displaying the same layout from a saved table also would have been easier.
I am now trying to get the minimum rate searched and highlighted for each row. Lets see.
Thanks
-
Sep 12th, 2014, 12:47 PM
#22
Re: From Excel To Listview
Another possible advantage of using rows rather than columns is the ability to sort the data by a column value, though you would need to format the prices to where they are all the same number of digits by adding leading 0s as needed.
-
Sep 14th, 2014, 05:19 AM
#23
Thread Starter
Member
Re: From Excel To Listview
Thanks,
I wrote the code for comparing the lowest Unit Rate and highlight that particular amount. It seems to have worked for the first two rows. Then it has skipped some rows and highlighted a zero amount (which is understandable since its the lowest). However, thereafter it has skipped the other rows or not highlighted any values. Attaching the screenshot and posting the processing code as below:
Private Sub Command1_Click()
Dim minv As Double
Dim i, totalcolumns As Integer
Dim LowestAmountColNo, LowestAmountRowNo As Integer
Dim ColToCheck As Integer
ColToCheck = 4
minv = 1000000
Label1.Caption = lvwComparativeStatement.ColumnHeaders.Count
With lvwComparativeStatement
For i = 1 To .ListItems.Count
For totalcolumns = 1 To lvwComparativeStatement.ColumnHeaders.Count
If .ColumnHeaders(totalcolumns).Text = "Unit Rate" Then
If CDbl(.ListItems(i).ListSubItems(totalcolumns - 1).Text) < minv Then
minv = CDbl(.ListItems(i).ListSubItems(totalcolumns - 1).Text)
LowestAmountColNo = totalcolumns - 1
LowestAmountRowNo = i
Else
LowestAmountColNo = LowestAmountColNo
LowestAmountRowNo = LowestAmountRowNo
End If
End If
Next totalcolumns
.ListItems(LowestAmountRowNo).ListSubItems(LowestAmountColNo).ForeColor = vbRed
Next i
End With
lvwComparativeStatement.Refresh
End Sub
What did I miss? Mental block!!
-
Sep 14th, 2014, 07:13 AM
#24
Re: From Excel To Listview
You're minv variable is persistent in the loop, so you are comparing against lowest value for all previous rows.
My comments on your code:
Code:
Private Sub Command1_Click()
Dim minv As Double
Dim i, totalcolumns As Integer ' i is Variant, you need to indicate type for each variable
Dim LowestAmountColNo, LowestAmountRowNo As Integer ' likewise, LowestAmountColNo is Variant
Dim ColToCheck As Integer
ColToCheck = 4
minv = 1000000 ' this variable needs to be reset for each row
Label1.Caption = lvwComparativeStatement.ColumnHeaders.Count ' put this inside With block
With lvwComparativeStatement
For i = 1 To .ListItems.Count
' reset minv variable here
' (next line) lvwComparativeStatement not needed in With block
For totalcolumns = 1 To lvwComparativeStatement.ColumnHeaders.Count
If .ColumnHeaders(totalcolumns).Text = "Unit Rate" Then
' You should ensure that only non-zero values are considered
If CDbl(.ListItems(i).ListSubItems(totalcolumns - 1).Text) < minv Then
minv = CDbl(.ListItems(i).ListSubItems(totalcolumns - 1).Text)
LowestAmountColNo = totalcolumns - 1
LowestAmountRowNo = i ' row-by-row comparison, row number always = i
Else ' this Else block does nothing and should be omitted
LowestAmountColNo = LowestAmountColNo
LowestAmountRowNo = LowestAmountRowNo
End If
End If
Next totalcolumns
' just use the loop variable for RowNo
.ListItems(i).ListSubItems(LowestAmountColNo).ForeColor = vbRed
Next i
End With
lvwComparativeStatement.Refresh ' put this inside With block
End Sub
My modification of your code. Obviously, I don't have the data, so I cannot test this code, but it should work as intended.
Code:
Private Sub Command1_Click()
Dim MinVal As Double, ThisVal As Double
Dim Row As Integer, Col As Integer
Dim FirstCol As Integer, BestCol As Integer
With lvwComparativeStatement
Label1.Caption = .ColumnHeaders.Count
' Find first "Unit Rate" column
For Col = 1 To .ColumnHeaders.Count
If .ColumnHeaders(Col).Text = "Unit Rate" Then Exit For
Next Col
FirstCol = Col
For Row = 1 To .ListItems.Count
' reset MinVal and BestCol for each row
MinVal = CDbl(.ListItems(Row).ListSubItems(FirstCol - 1).Text)
BestCol = FirstCol
' We have FirstCol, and know that "Unit Rate" is every third column thereafter
' Take advantage of this and avoid checking column headers for every row
For Col = FirstCol + 3 To .ColumnHeaders.Count Step 3
ThisVal = CDbl(.ListItems(Row).ListSubItems(Col - 1).Text)
If ThisVal = 0 Then Exit For ' no more values to check
If ThisVal < MinVal Then
MinVal = ThisVal
BestCol = Col
End If
Next Col
' for consistancy, maintain (Col - 1) SubItems index reference
.ListItems(Row).ListSubItems(BestCol - 1).ForeColor = vbRed
Next Row
.Refresh
End With
End Sub
One additional note: you will need to reset .ForeColor when repopulating the listview.
-
Sep 14th, 2014, 10:13 AM
#25
Thread Starter
Member
Re: From Excel To Listview
Hi Logo,
Thanks for the correction. I did use the Col+3 but did not put it correctly initially. However, from your corrections are just about near perfect. Refer the last 3 rows (particularly 18th and 19th). It highlights the Zero values instead of the lowest. Have'nt changed anything in your correction. Thanks a lot!!!
-
Sep 16th, 2014, 03:15 AM
#26
Re: From Excel To Listview
I must have assumed that the first column would not contain a zero value. Easy to fix:
Code:
For Row = 1 To .ListItems.Count
MinVal = 0
For Col = FirstCol To .ColumnHeaders.Count Step 3
ThisVal = CDbl(.ListItems(Row).ListSubItems(Col - 1).Text)
If ThisVal > 0 Then
If ThisVal < MinVal Or MinVal = 0 Then
MinVal = ThisVal
BestCol = Col
End If
End If
Next Col
If MinVal > 0 Then .ListItems(Row).ListSubItems(BestCol - 1).ForeColor = vbRed
Next Row
-
Sep 16th, 2014, 05:04 AM
#27
Thread Starter
Member
Re: From Excel To Listview
Thanks Logo,
It worked perfectly. Thanks to you and DataMiser for the valuable contribution and tips.
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
|