Results 1 to 6 of 6

Thread: Context Sensitive Form extracting Data from Excel Worksheet

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Context Sensitive Form extracting Data from Excel Worksheet

    Using: Microsoft Visual Studio Community 2015, Version 14.0.24720.00 Update 1, with Microsoft .NET Framework Version 4.6.01055

    Language: Visual Basic 2015
    Target: Windows 7 application

    Objective: Open Excel Worksheet from shared Box.com account, and get all of the data on the sheet (used range) and save into application. Populate ComboBoxes with columns from the excel data, filtering out duplicate data points from the dropdown lists. Auto complete for each ComboBox further filtering the list when user inputs text into the ComboBox. Selection in any ComboBox further filtering the list items in all other ComboBoxes on the form. Save button writes selected (or typed in the case of new data) from ComboBoxes to the first unused row of the Excel Worksheet (the same worksheet from above).

    Completed: My code is currently opening the Worksheet and bringing the data into the application. I am bringing the Excel data into a 2-dimensional array. I have created event handlers and autocomplete for the ComboBoxes so they are all populating from the columns (from the array data copied from the Worksheet). My current code also writes data back to the Excel Worksheet directly from the values in the ComboBoxes.

    The struggle I am having is with manipulating the Array Data after extracting it from the Excel Worksheet. I am unable to redimension and preserve a new array to collect each row of array data that matched a ComboBoxes selection.

    The excel data is related to quote numbers. a Short example is:
    a1: Quote Number, b1: Date/Time Stamp (when last saved), c1: Client, d1, Item 1 Cost... 73 more data points.

    Each data point is populated into the comboboxes, and duplicates are filtered out. The user needs to be able to further filter the comboboxes by completing a selection in any of the comboboxes on the form. So if the user selects a client, then only the quote numbers in the same row (from the excel worksheet) would populate in the comboboxes. If the user then selects an Item from the Items combobox, the quote numbers would filter further (rebuild the list of items in the combobox). The end result is that the user would only need to know a couple data points to filter out all of the comboboxes to resolve at the only possible option. I would then like to turn all of the comboboxes background color a light shade of green to acknowledge to the user that there are no further options, and populate all of the selections of all remaining comboboxes with the only options remaining.

    I do not know a lot about working with 2-dimensional dictionaries or lists(of lists). I have tried, but failed since almost all of the searches I have dont work explicitly with 1 dimensional data or dictionaries of dictionaries (1 dimensional populated by 1 dimensional). The problem I run into is how to get the excel data into that where I can then still work with the "Rows" and "Columns". In a 2-diminsional array its just a loop Array(i, 1).

  2. #2

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Re: Context Sensitive Form extracting Data from Excel Worksheet

    Code for extracting the data from the Excel Worksheet into a global 2-Dimensional array (myData):

    Code:
    Sub myGetArray(ByVal myWB As Integer, mySheetNumber As Integer)
            Dim myUser As String = Environ("USERPROFILE")
            Dim myDBSubPath As String = "\Box Sync\Tech Writer Projects\Quote Interface\"
            Dim myDBFileName As String = "QuoteFakeDatabase.xlsx"
            Dim myPBFileName As String = "TestPB.xlsx"
            Dim myDBFullPath As String = Nothing
    
            If myWB = 1 Then
                myDBFullPath = myUser & myDBSubPath & myDBFileName
            ElseIf myWB = 2 Then
                myDBFullPath = myUser & myDBSubPath & myPBFileName
            End If
    
            Dim myExcel As Excel.Application = New Excel.Application
            Dim myWB1 As Excel.Workbook = myExcel.Workbooks.Open(myDBFullPath,, True)
            Dim mySheet As Excel.Worksheet = myWB1.Sheets(mySheetNumber)
            Dim myRange As Excel.Range = mySheet.UsedRange
    
            If myWB = 1 Then
                myData = myRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault)
            ElseIf myWB = 2 Then
                SSData = myRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault)
            End If
           
            myWB1.Close(False)
            myExcel.Quit()
            releaseObject(myExcel)
            releaseObject(myWB1)
            releaseObject(mySheet)
    
        End Sub
    NOTE: this sub is called from my forms load event. It will also be called from a form reset button that is not currently included (and not part of the problem).
    Last edited by JoeyL; Jan 11th, 2016 at 11:59 AM.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Re: Context Sensitive Form extracting Data from Excel Worksheet

    Code for populating the Comboboxes on the form from the myData array:

    Code:
    Sub myDropdownFill2(ByVal sender As Object, myWB As Integer)
    
            Dim myTempArray(,) = Nothing
    
            If myWB = 1 Then
                myTempArray = myData
            ElseIf myWB = 2 Then
                myTempArray = SSData
            End If
    
            Dim myLastRow As Integer = myTempArray.GetUpperBound(0) ' Last Row
            Dim myFirstRow As Integer = myTempArray.GetLowerBound(0) ' First Row
            Dim myLastColumn As Integer = myTempArray.GetUpperBound(1) ' Last Column
            Dim myFirstColumn As Integer = myTempArray.GetLowerBound(1) ' First Column
            Dim myCB As ComboBox = Nothing
    
            For myi As Integer = myFirstColumn To myLastColumn ' Loop through first to last column of the myData array
                Try
                    If sender = "LOAD" Then ' If the Form Load called this sub
                        myCB = Controls.Find(myTempArray(1, myi), True)(0) ' Finds controls related to database row1
                    Else
                        myCB = CType(sender, ComboBox) ' Otherwise, the control is the ControlExpanded event sender for ComboBoxes
                    End If
                    If Not myCB Is Nothing Then ' If the Control exists
                        For myj As Integer = myFirstRow To myLastRow ' Loop through each array element by row
                            If Not myTempArray(myj, myi) = Nothing Then ' and if the array element in not empty/nothing
                                If Not myCB.Items.Contains(myTempArray(myj, myi)) Then ' and if the array element does not already exist to the ComboBox items list
                                    myCB.Items.Add(myTempArray(myj, myi)) ' Then add the array element to the ComboBox
                                End If
                            End If
                        Next myj
                    End If
                Catch
                End Try
            Next myi
        End Sub
    NOTE: not part of the problem, however, the myData and SSData are 2 different Excel Workbooks which I am reading data from into two different arrays.

    NOTE 2: sender is the expand event of all comboboxes, and passes the switch for which excel workbook to open based on the comboboxes name value. The comboboxes are all named identically to their related excel column text (so excel cell A1.value and ComboBox1.name are identical.)
    Last edited by JoeyL; Jan 11th, 2016 at 12:04 PM.

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Re: Context Sensitive Form extracting Data from Excel Worksheet

    Current code for trying to get the matches from the selection in a combobox by transplanting data from myData array to TempArray, where comboboxes can be repopulated from the condensed list:

    This is where I am struggling. Maybe my whole process needs to be reconsidered from the excel data extraction?

    Code:
    Sub myReadArrayandExtractRange()
            Dim bound0 As Integer = myData.GetUpperBound(0) ' Rows
            Dim bound1 As Integer = myData.GetUpperBound(1) ' Columns
            Dim mySender As String = QuoteNumber.Text ' name of combobox and excel a1 - Change later to event handler
            Dim myDBColumn As Integer = 1 ' excel column number - Change later to event handler pass
            Dim tempArrayRows As Integer = 0 ' Count of matches in loop k
    
            ' k loop counts sender matches in myData column X for dimensioning TempArray
            For k As Integer = 1 To bound0
                If myData(k, myDBColumn).ToString = mySender Then
                    tempArrayRows = tempArrayRows + 1
                End If
            Next k
    
            'Dimension TempArray from matches
            Dim tempArray(tempArrayRows, bound1)
    
            ' i loop iterates each row in myData looking for a match to sender
            For i As Integer = 1 To bound0
                If myData(i, myDBColumn).ToString = mySender Then
                    ' ji loop changes to each row in TempArray
                    For ji = 1 To tempArrayRows
                        ' j loop moves across a row to each column of data and write the data to TempArray
                        For j As Integer = 1 To bound1
                            tempArray(ji, j) = myData(i, j)
                        Next j
                    Next ji
                End If
            Next i
        End Sub

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Re: Context Sensitive Form extracting Data from Excel Worksheet

    No one?

  6. #6

    Thread Starter
    New Member
    Join Date
    Jul 2011
    Posts
    11

    Re: Context Sensitive Form extracting Data from Excel Worksheet

    Anyone?

Tags for this Thread

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