VB6 - Load creation

Hi
Once the Excel in attachment is uploaded in the MSHFlexgrid, I would like to create loads based on 5 columns. Columns identified in below snapshot.
Loads needs to be created based on Origin Country, Destination State, Commodity, Source ID and Dest_ID.
What you will also see is a TextBox that will tell how many Stops should be set Per Loads.
During the load creation, it will take a look at column 1, 2, 3, 4 combination for the first step, then, it will take from DEST_ID (Column no 5 in below snapshot) the first 5 distinct numbers to create a load. In the columns 1, 2, 3, 4 and 5, you can have multiple times the same numbers as they are different orders IDs from column ID.

Load-criteria-1.jpg
Once it found the distinct 5 DEST_ID numbers with same values from column 1, 2, 3, 4 combination, it will name the load based on column number 2 and 3 from above snapshot.
Ex:
If the first 5 distinct numbers from column 5 and column 2 = AB and column 3 = DRY, the first load would be called AB_DRY_1. If I have another 5 distinct DEST_ID with the same values in columns 1, 2, 3, 4, it will name it AB_DRY_2.
It may happen where we may have less than 5 different numbers to create a load. In this case, it will only be 1 load.

In below snapshot, it shows the results it should gave.
The first 4 rows have the same values Source_id: 35, Dest_ID: 04, Origin Country: Canada, Destination_State: AB and Commodity: DRY. In this case, it will build a load with only 1 stop with load ID called: AB_DRY_1.

Load-criteria-result.jpg
How can these load IDs be created?

In attachment, you will found a sample of the project and also an excel file that you can use to populate the grid (sheet Data Set) and also an example of the final result on sheet FINAL RESULT.
Thanks for your help.
Load-creator.zip
template.xlsx
LVL 11
Wilder1626Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Wilder, what are the steps that I take to load the data in the flexgrid? Here's what I did.
I added code into Form1's load event to place the path and name of your template file into text3. When I then click "Load into Flexgrid" I gat a "Select the worksheet!" error message. What amd I doing wrong?
Wilder1626Author Commented:
HI Martin Liss

in the combo box beside the "Load into Flexgrid" the  you need to select the sheet: DATA SET.

Then you click on "Load into Flexgrid".

upload file
Martin LissOlder than dirtCommented:
Thanks but I needed to click Command1 first because otherwise the combobox is empty.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Martin LissOlder than dirtCommented:
OK got it loaded and now the fun begins:)
Martin LissOlder than dirtCommented:
Try this. If you need help with your error checking let me know.
Private Sub Command2_Click()
'Form8.Show (False)


    On Error GoTo step_error
    Dim XLS As New Excel.Application
    Dim WRK As Excel.Workbook
    Dim SHT As Excel.Worksheet
    Dim RNG As Excel.Range

    Dim ArrayCells() As Variant
Option1.Value = True
    If Combo2.ListIndex <> -1 Then
        'Create a new instance of Excel
        Set XLS = CreateObject("Excel.Application")
        'Open the XLS file. The two parameters representes, UpdateLink = False and ReadOnly = True. These parameters have this setting to dont occur any error on broken links and allready opened XLS file.
        Set WRK = XLS.Workbooks.Open(Text3.Text, False, True)
        'Set the SHT variable to selected worksheet
        Set SHT = WRK.Worksheets(Combo2.List(Combo2.ListIndex))

        'Get the used range of current worksheet
        Set RNG = SHT.UsedRange

        'Change the dimensions of array to fit the used range of worksheet
        ReDim ArrayCells(1 To RNG.Rows.Count, 1 To RNG.Columns.Count)

        'Transfer values of the used range to new array
        If Option1.Value Then
            ArrayCells = RNG.Value
        ElseIf Option2.Value Then
            ArrayCells = RNG.Formula
        End If

        'Close worksheet
        WRK.Close False
        'Quit the MS Excel
        XLS.Quit

        'Release variables
        Set XLS = Nothing
        Set WRK = Nothing
        Set SHT = Nothing
        Set RNG = Nothing

        'Configure the flexgrid to display data
        MSHFlexGrid_order_extract.Redraw = False
        MSHFlexGrid_order_extract.FixedCols = 0
        MSHFlexGrid_order_extract.FixedRows = 1
        MSHFlexGrid_order_extract.Rows = UBound(ArrayCells, 1)
        MSHFlexGrid_order_extract.Cols = UBound(ArrayCells, 2)
Dim r As Integer
Dim c As Integer
        For r = 0 To UBound(ArrayCells, 1) - 1
            For c = 0 To UBound(ArrayCells, 2) - 1
                MSHFlexGrid_order_extract.TextMatrix(r, c) = CStr(ArrayCells(r + 1, c + 1))
            Next
        Next
        MSHFlexGrid_order_extract.Redraw = True
    Else
        MsgBox "Select the worksheet!", vbCritical
        Combo2.SetFocus
    End If

    'Auto column fit
    Dim cell_wid As Single
    Dim col_wid As Single

    For c = 0 To MSHFlexGrid_order_extract.Cols - 1
        col_wid = 0
        For r = 0 To MSHFlexGrid_order_extract.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid_order_extract.TextMatrix(r, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next r
        MSHFlexGrid_order_extract.ColWidth(c) = col_wid + 120
    Next c

    'Count records
    Dim z As Long, txt As String, total As Long
    For z = 1 To MSHFlexGrid_order_extract.Rows - 1
        If Len(MSHFlexGrid_order_extract.TextMatrix(z, 3)) Then total = total + 1
    Next z
    lblTotalrecord1 = CStr(total)

    '---------- Align MSHFlexgrid ----------------
    With MSHFlexGrid_order_extract
        Dim k As Long
        For k = 0 To .Cols - 1
            .ColAlignment(k) = flexAlignLeftCenter
        Next
    End With


    'new
    Dim lngRow As Long
    Dim intStopCount As Integer
    Dim strKey As String
    Dim strDest As String
    Dim intSuffix As Integer
    With MSHFlexGrid_order_extract
        strKey = .TextMatrix(1, 5) & .TextMatrix(1, 55) & .TextMatrix(1, 56) & .TextMatrix(1, 57)
        strDest = .TextMatrix(1, 6)
        intStopCount = 1
        intSuffix = 1
        For lngRow = 1 To .Rows - 1
            If strKey = .TextMatrix(lngRow, 5) & .TextMatrix(lngRow, 55) & .TextMatrix(lngRow, 56) & .TextMatrix(lngRow, 57) Then
                If strDest <> .TextMatrix(lngRow, 6) Then
                    intStopCount = intStopCount + 1
                    If intStopCount > stop_load.Text Then
                        intStopCount = 1
                        intSuffix = intSuffix + 1
                    End If
                    strDest = .TextMatrix(lngRow, 6)
                End If
            Else
                intStopCount = 1
                intSuffix = 1
                strKey = .TextMatrix(lngRow, 5) & .TextMatrix(lngRow, 55) & .TextMatrix(lngRow, 56) & .TextMatrix(lngRow, 57)
            End If
            .TextMatrix(lngRow, 59) = .TextMatrix(lngRow, 56) & "_" & .TextMatrix(lngRow, 57) & "_" & intSuffix
        Next
    End With
    
   ' Unload Form8


    Call MsgBox("File now uploaded in grid", vbInformation Or vbSystemModal Or vbMsgBoxRight Or vbMsgBoxRtlReading, "Excel file uploaded")



    Exit Sub
step_error:
    MsgBox Err.Number & " - " & Err.Description
    Resume Next

End Sub

Open in new window

Wilder1626Author Commented:
I've just did the test and i think we are almost there.

A load should be a combination of column 1,2,3,4 identified in my first snapshot. then, it needs to take the DEST_ID column to find the e store destinations to make a load.

Just to do some test, i made some changes in the excel data file to have multiple different SOURCE ID from same country. It is trying to merge inside the same load different source ID when it should be the same one per load.
Ex:
result_one
Martin LissOlder than dirtCommented:
Could you attach a workbook with the modified data?
Wilder1626Author Commented:
sure, this is the template 2 with what should also be the final results.
template-2.xlsx
Wilder1626Author Commented:
I'm doing some test like this in below code.

I think it is working if i include the SOURCE_ID in the load_id.  (Row 27 of below code)

I will continue the test and let you know.

    'new
    Dim lngRow As Long
    Dim intStopCount As Integer
    Dim strKey As String
    Dim strDest As String
    Dim intSuffix As Integer
    With MSHFlexGrid_order_extract
        strKey = .TextMatrix(1, 5) & .TextMatrix(1, 55) & .TextMatrix(1, 56) & .TextMatrix(1, 57)
        strDest = .TextMatrix(1, 6)
        intStopCount = 1
        intSuffix = 1
        For lngRow = 1 To .Rows - 1
            If strKey = .TextMatrix(lngRow, 5) & .TextMatrix(lngRow, 55) & .TextMatrix(lngRow, 56) & .TextMatrix(lngRow, 57) Then
                If strDest <> .TextMatrix(lngRow, 6) Then
                    intStopCount = intStopCount + 1
                    If intStopCount > stop_load.Text Then
                        intStopCount = 1
                        intSuffix = intSuffix + 1
                    End If
                    strDest = .TextMatrix(lngRow, 6)
                End If
            Else
                intStopCount = 1
                intSuffix = 1
                strKey = .TextMatrix(lngRow, 5) & .TextMatrix(lngRow, 55) & .TextMatrix(lngRow, 56) & .TextMatrix(lngRow, 57)
            End If
             .TextMatrix(lngRow, 59) = .TextMatrix(lngRow, 5) & "_" & .TextMatrix(lngRow, 56) & "_" & Left$(.TextMatrix(lngRow, 57), 1) & "_" & intSuffix
        Next
    End With

Open in new window

Martin LissOlder than dirtCommented:
I'm need some clarification. In your description of the requirements you say that the first step is to look at the first 4 of your numbered columns which are Origin_Country, Destination_State, Commodity and Source_ID. Let's call that the "key". I assume then that every time I read a row that I should compare the previous key with the new one and  when I find a difference I should do something.

I know that the rows 1 to 4 have the same key and so I put AB_DRY_1 (let's call the "_1" part the "suffix") in the Load_Number column, and when I get to row 5 the key is different so I reset the suffix to _1 (even though in this case it already is) and I put BC_DRY_1 in Load_Number. The key is the same until I get to row 22 and here's my confusion. In your last picture you indicate that the next three rows should be their own block and I assume that the Load_Number should be BC_DRY_2, but how do I tell the difference between this situation and the one at row 6?

Writing this has caused me to think that I shouldn't use Origin_Country, Destination_State, Commodity and Source_ID as the key but rather just Origin_Country, Destination_State, Commodity, and if that key is the same but the Source_ID is different the Load_Number would go from BC_DRY_1 to BC_DRY_2. Is that logical?
Martin LissOlder than dirtCommented:
We cross-posted but great minds think alike:)
Martin LissOlder than dirtCommented:
I guess we can say that the first two parts of the Load_Number are always the Destination_State and the Commodity so we don't have to concern ourselves with them. So if you could state the conditions when the suffix should change it would make it easier for me.
Wilder1626Author Commented:
You make me think about it more in detail.

Let say that these columns are the primary keys:
1- Origin_Country      
2- Destination_State      
3- Commodity

Then, per distinct SOURCE_ID, it will put per load 5 different DEST_ID's.

I should never see 5 DEST_IDs with 2 different SOURCE_ID's or more.

But if the primary keys are a like but we have 2 SOURCE_ID's, that will automatically create 2 loads. One for the first SOURCE, up to 5 DEST_ID's and the second loads to the second SOURCE_ID.

Hope this makes more sense in my explanation.
Martin LissOlder than dirtCommented:
See if this is better.
Private Sub Command2_Click()
'Form8.Show (False)


    On Error GoTo step_error
    Dim XLS As New Excel.Application
    Dim WRK As Excel.Workbook
    Dim SHT As Excel.Worksheet
    Dim RNG As Excel.Range

    Dim ArrayCells() As Variant
Option1.Value = True
    If Combo2.ListIndex <> -1 Then
        'Create a new instance of Excel
        Set XLS = CreateObject("Excel.Application")
        'Open the XLS file. The two parameters representes, UpdateLink = False and ReadOnly = True. These parameters have this setting to dont occur any error on broken links and allready opened XLS file.
        Set WRK = XLS.Workbooks.Open(Text3.Text, False, True)
        'Set the SHT variable to selected worksheet
        Set SHT = WRK.Worksheets(Combo2.List(Combo2.ListIndex))

        'Get the used range of current worksheet
        Set RNG = SHT.UsedRange

        'Change the dimensions of array to fit the used range of worksheet
        ReDim ArrayCells(1 To RNG.Rows.Count, 1 To RNG.Columns.Count)

        'Transfer values of the used range to new array
        If Option1.Value Then
            ArrayCells = RNG.Value
        ElseIf Option2.Value Then
            ArrayCells = RNG.Formula
        End If

        'Close worksheet
        WRK.Close False
        'Quit the MS Excel
        XLS.Quit

        'Release variables
        Set XLS = Nothing
        Set WRK = Nothing
        Set SHT = Nothing
        Set RNG = Nothing

        'Configure the flexgrid to display data
        MSHFlexGrid_order_extract.Redraw = False
        MSHFlexGrid_order_extract.FixedCols = 0
        MSHFlexGrid_order_extract.FixedRows = 1
        MSHFlexGrid_order_extract.Rows = UBound(ArrayCells, 1)
        MSHFlexGrid_order_extract.Cols = UBound(ArrayCells, 2)
Dim r As Integer
Dim c As Integer
        For r = 0 To UBound(ArrayCells, 1) - 1
            For c = 0 To UBound(ArrayCells, 2) - 1
                MSHFlexGrid_order_extract.TextMatrix(r, c) = CStr(ArrayCells(r + 1, c + 1))
            Next
        Next
        MSHFlexGrid_order_extract.Redraw = True
    Else
        MsgBox "Select the worksheet!", vbCritical
        Combo2.SetFocus
    End If

    'Auto column fit
    Dim cell_wid As Single
    Dim col_wid As Single

    For c = 0 To MSHFlexGrid_order_extract.Cols - 1
        col_wid = 0
        For r = 0 To MSHFlexGrid_order_extract.Rows - 1
            cell_wid = TextWidth(MSHFlexGrid_order_extract.TextMatrix(r, c))
            If col_wid < cell_wid Then col_wid = cell_wid
        Next r
        MSHFlexGrid_order_extract.ColWidth(c) = col_wid + 120
    Next c

    'Count records
    Dim z As Long, txt As String, total As Long
    For z = 1 To MSHFlexGrid_order_extract.Rows - 1
        If Len(MSHFlexGrid_order_extract.TextMatrix(z, 3)) Then total = total + 1
    Next z
    lblTotalrecord1 = CStr(total)

    '---------- Align MSHFlexgrid ----------------
    With MSHFlexGrid_order_extract
        Dim k As Long
        For k = 0 To .Cols - 1
            .ColAlignment(k) = flexAlignLeftCenter
        Next
    End With


    'new
    Dim lngRow As Long
    Dim intStopCount As Integer
    Dim strKey As String
    Dim strDest As String
    Dim intSuffix As Integer
    Dim strSourceID As String
    Const SOURCE_ID = 5
    Const DEST_ID = 6
    Const KEY1 = 55
    Const KEY2 = 56
    Const KEY3 = 57
    Const LOAD_NBR = 59
    
    With MSHFlexGrid_order_extract
        strKey = .TextMatrix(1, KEY1) & .TextMatrix(1, KEY2) & .TextMatrix(1, KEY3)
        strDest = .TextMatrix(1, DEST_ID)
        strSourceID = .TextMatrix(1, SOURCE_ID)
        intStopCount = 1
        intSuffix = 1
        For lngRow = 1 To .Rows - 1
            If strKey = .TextMatrix(lngRow, KEY1) & .TextMatrix(lngRow, KEY2) & .TextMatrix(lngRow, KEY3) Then
                If strSourceID = .TextMatrix(lngRow, SOURCE_ID) Then
                    If strDest = .TextMatrix(lngRow, DEST_ID) Then
                        'continue
                    Else
                        strDest = .TextMatrix(lngRow, DEST_ID)
                        intStopCount = intStopCount + 1
                        If intStopCount > 5 Then
                            ' There have been more than 5 destinations for this key
                            ' so reset the count and increment the suffix
                            intStopCount = 1
                            intSuffix = intSuffix + 1
                        End If
                    End If
                Else
                    ' The source id has changed so
                    intSuffix = intSuffix + 1
                    strSourceID = .TextMatrix(lngRow, SOURCE_ID)
                End If
            Else
                ' The key is different so reset everything
                strKey = .TextMatrix(lngRow, KEY1) & .TextMatrix(lngRow, KEY2) & .TextMatrix(lngRow, KEY3)
                strDest = .TextMatrix(lngRow, DEST_ID)
                strSourceID = .TextMatrix(lngRow, SOURCE_ID)
                intStopCount = 1
                intSuffix = 1
            End If
            .TextMatrix(lngRow, LOAD_NBR) = .TextMatrix(lngRow, KEY2) & "_" & .TextMatrix(lngRow, KEY3) & "_" & intSuffix
        Next
    End With
    
   ' Unload Form8


    Call MsgBox("File now uploaded in grid", vbInformation Or vbSystemModal Or vbMsgBoxRight Or vbMsgBoxRtlReading, "Excel file uploaded")



    Exit Sub
step_error:
    MsgBox Err.Number & " - " & Err.Description
    Resume Next

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
Something is weird. Everything looks good accept for 1 record

Same Source id and same dest_id, but one of them is all alone.

In the excel file, i don't see any differences between this source and dest_id compare to the others. It should of been merged in the same load id.

result no 3
Martin LissOlder than dirtCommented:
Add strDest = .TextMatrix(lngRow, DEST_ID) after line 131.

I also notice that in order to fill the combobox you need to click it twice. To change that add the 'new line in this part of the Command1_Click code.

        For Each SHT In WRK.Worksheets
            'Put the name of worksheet in combo
            Combo2.AddItem SHT.Name
        Next
        'new
        Combo2.ListIndex = 0

Open in new window

Wilder1626Author Commented:
Thank you so much again for your help. this is exactly what i needed.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest youincluding these two new ones.
An Experts Exchange Shortcut for the Truly Lazy (for Apple OS X)
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.