I’ve got a desktop app that reads from an Excel spreadsheet to a list box. It is really slow, which I expect is due to Excel not Xojo.
Anyone have any ideas on how to speed it up?
Did you measure where the slow speed comes from? With the Profiler on Windows or with Instruments on the Mac.
Do you read everything at once or line by line? Which version of Excel do you read in?
dont ‘move’ the selection from cell to cell in Excel while getting the data.
(Refer to the cells like arrays.)
Hide excel while you are using it.
Dont redraw the listbox while you are updating it (maybe store the data into memory then fill the listbox from that, and make the listbox invisible while filling it)
Your code will probably have some bearing, but you didnt post any yet… 
If you are reading tables (named ranges) from Excel, then probably accessing the data with an odbc-connection would be faster.
Hello John,
Here is code which opens excel, writes 16,000 data points to Excel, Reads 16,000 data points from Excel, and populates a combobox with 16,000 points of data. Opening, writing, reading, and populating takes about 3.5 seconds. Here is code for this example:
[code] Dim excel as new ExcelApplication
Dim i as integer
Dim s as string
Dim r as ExcelRange
Dim StartTime as Double = Microseconds
//Create Data
For i = 1 to 16000
s = s + “A” + cstr(i) + “B” + EndOfLine.Windows
Next
//Copy to clipboard
Dim c as new Clipboard
c.SetText(s)
c.Close
//Paste from Clipboard to Excel
excel.Visible = true
excel.Workbooks.Add
r = excel.Range(“A1:A16000”)
r.Select_
excel.ActiveSheet.PasteSpecial(“Text”,false,false)
//MsgBox “16,000 datapoints added to excel”
//Clear the Clipboard
c.Text = “”
TextArea1.Text = c.Text
c.Close
//MsgBox “Clipboard cleared”
//Copy from Excel to Clipboard
excel.Range(“A1:A16000”).Copy
If c.TextAvailable then
TextArea1.Text = c.Text
End if
c.Close
//MsgBox “16,000 data points from Excel to clipboard”
//Put copied data into ComboBox
Dim Lines() as String
Lines = Split(TextArea1.Text, chr(13))
For i = 1 to UBound(Lines)
ComboBox1.AddRow Lines(i)
Next i
//MsgBox “16,000 data points added to Combobox”
Dim ThisTime as Double = Microseconds
MsgBox CStr((ThisTime-StartTime)/1000000) + " Seconds"
//Show elapsed time
Exception err
MsgBox err.message[/code]
Wow . . . Great idea Eugene . . . only one read from Excel gets around all the wasted time (15 minutes down to 3-4 seconds. I tweaked your routine a bit because I’m reading 500-800 rows with 50-60 columns each:
Sheet.Activate
lstSkills.DeleteAllRows
lstSkills.HasHeading=true
EndColumn=1
CellContents=""
While Sheet.Cells(1,EndColumn).Text <> “" 'get number of columns (last column has "”
EndColumn = EndColumn+1 could also be done with search for
Wend cell.text = “”)
EndColumn = EndColumn - 1
lstSkills.ColumnCount = EndColumn 'set number of columns
i = 2 'get number of rows
While Sheet.Cells(i,1).Text <> “”
EndRow = Sheet.Cells(i,1).Row
i=i+1
wend
dim c as new clipboard
dim word, line as string
dim start as integer = 0
dim lines(), words() as string
c.close 'get data from Excel
sheet.range(sheet.cells(1,1),sheet.cells(endRow,endColumn)).copy
textArea1.text = c.text
lines = split(c.text,chr(13)) 'split data into lines of data
words = split(lines(0),chr(9))
for j = 0 to words.ubound 'create column headings from row 1 of sheet
lstSkills.heading(j) = words(j)
next j
for i = 1 to lines.ubound - 1 'write rows of data to listbox
words = split(lines(i),chr(9)) 'split each line into columns
lstSkills.addRow
for j = 0 to words.ubound
lstSkills.cell(lstskills.lastIndex,j) = words(j)
next j
next i