I’m working on my first real Xojo project and am struggling some. My current problem is getting Xojo to populate a dictionary correctly from data in Excel.
I’m using the concepts from this previous post: https://forum.xojo.com/25535-speed-up-read-from-excel
One of the sheets in the Excel workbook (named “TASK”) lists schedule activities with their percent complete information. Those are the two columns I want, and are automatically named “Activity ID” and “Activity % Complete(%)” by the project scheduling program I use. There are other columns of information on that sheet, but I’m ignoring all that other stuff for now as I try to build my dictionary.
I’m using some sample data that has only 2 rows of data… I’ve not tried with a production size sheet yet which would have 100 to 10,000 rows.
[code]Activity ID | % Complete |
---|---|
A1010 | 50 |
A1020 | 0[/code] |
The problem I’m having is that the Key doesn’t get recorded by Xojo for the second row of data… it’s just null (blank) field, and I cannot figure out why.
(By the way, I have other methods that open and do some error checking before this method is called.)
[code]Dim excel As New ExcelApplication
Dim TempDictionary As New Dictionary
Dim c As New Clipboard
Dim Lines() As String
Dim CellValues() As String
Dim ActIDCol As Integer
Dim PcntCompletCol As Integer
Dim LastRow As Integer
excel.ActiveWorkbook.worksheets(“TASK”).activate
ActIDCol = excel.cells.find(“Activity ID”).Column
PcntCompletCol = excel.cells.find(“Activity % Complete(%)”).column
LastRow = excel.activesheet.usedrange.rows.count +1
//copy the whole sheet into the clipboard
excel.range(excel.cells(3,ActIDCol),excel.cells(lastrow,PcntCompletCol)).copy
lines = Split(c.Text,Chr(13)) //splitting into rows of data
Dim i As Integer
For i = 0 To lines.ubound-1
CellValues = Split(lines(i),Chr(9)) //splitting the row of data into columns
'MsgBox(CellValues(actidcol-1)) + Chr(9) + CellValues(PcntCompletCol-1)
TempDictionary.Value(CellValues(actidcol-1)) = CellValues(PcntCompletCol-1)
Next i
Return TempDictionary[/code]
A second problem is how to deal with numbers. Data in the spreadsheet is always going to be viewed by excel as text based on how it’s exported. I have no control over that. But, ultimately, I want the % Complete to be a double. When I try to convert it before populating the dictionary using CDbl(xxxx) , I get a different error saying that Xojo was expecting a string but got an integer.
Thank you for your help!!