-
Jan 11th, 2016, 10:34 AM
#1
Thread Starter
New Member
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).
-
Jan 11th, 2016, 11:47 AM
#2
Thread Starter
New Member
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.
-
Jan 11th, 2016, 11:49 AM
#3
Thread Starter
New Member
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.
-
Jan 11th, 2016, 11:53 AM
#4
Thread Starter
New Member
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
-
Jan 13th, 2016, 04:01 PM
#5
Thread Starter
New Member
Re: Context Sensitive Form extracting Data from Excel Worksheet
-
Jan 18th, 2016, 10:14 AM
#6
Thread Starter
New Member
Re: Context Sensitive Form extracting Data from Excel Worksheet
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|