Results 1 to 27 of 27

Thread: [RESOLVED] From Excel To Listview

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Resolved [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

  2. #2
    Hyperactive Member
    Join Date
    Jun 2014
    Location
    Lahore, Pakistan
    Posts
    450

    Re: From Excel To Listview

    There might b some problem with your code. Post the code!!!

  3. #3
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  4. #4

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: From Excel To Listview

    Quote Originally Posted by hamza.saleem View Post
    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

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: From Excel To Listview

    Hi Data,

    Thanks, but which line are you referring to?

  6. #6
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  7. #7

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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.

  8. #8
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  9. #9

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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.

  10. #10
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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?

  11. #11

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: From Excel To Listview

    Thanks again, Data

    Name:  ComparativeStatement.jpg
Views: 587
Size:  39.3 KB

    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

  12. #12
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  13. #13

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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.

  14. #14
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  15. #15

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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.

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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

  17. #17

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    Re: From Excel To Listview

    Thanks Data,

    Will try that and post the results.

  18. #18
    Frenzied Member
    Join Date
    Jun 2006
    Posts
    1,098

    Re: From Excel To Listview

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

  19. #19

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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

  20. #20
    Frenzied Member
    Join Date
    Jun 2006
    Posts
    1,098

    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.

  21. #21

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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

  22. #22
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    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.

  23. #23

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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!!
    Name:  Comparision_Bug.jpg
Views: 515
Size:  39.7 KB

  24. #24
    Frenzied Member
    Join Date
    Jun 2006
    Posts
    1,098

    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.

  25. #25

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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!!! Name:  Comparision_2.jpg
Views: 705
Size:  41.0 KB

  26. #26
    Frenzied Member
    Join Date
    Jun 2006
    Posts
    1,098

    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

  27. #27

    Thread Starter
    Member
    Join Date
    Jun 2008
    Posts
    41

    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
  •  



Click Here to Expand Forum to Full Width