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.
Activity ID | % Complete -------------|-------------- A1010 | 50 A1020 | 0
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.)
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
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!!