I am in the process of converting an application that ran fine in RB2012r2.1 to the latest version of xojo.
One of the features of our application is an import process that some users utilize on a regular basis. The latest issue I just discovered today is that the progress bar that I was using in RB will not work when importing from Excel in xojo. Yes, it was done the proper way (updated in a thread with a timer), but apparently xojo thinks that just reading from an Excel spreadsheet is a UI process and crashes the application when it tries to read the very first cell.
If I remove the progress bar, it imports fine, but when you have users who are import 30,000 rows with 250 columns, it’s pretty sad if you can’t provide some feedback during this hour long process.
[quote=84039:@John Fatte]I am in the process of converting an application that ran fine in RB2012r2.1 to the latest version of xojo.
One of the features of our application is an import process that some users utilize on a regular basis. The latest issue I just discovered today is that the progress bar that I was using in RB will not work when importing from Excel in xojo. Yes, it was done the proper way (updated in a thread with a timer), but apparently xojo thinks that just reading from an Excel spreadsheet is a UI process and crashes the application when it tries to read the very first cell.
[/quote]
Then this suggests that the set up is not correct as you claim
But without code to scan its hard to know why this might be the case
There were a number of programmers at the office that looked at this, but here are some code snippets to show the routine. Keep in mind that this is the same code that was running in RB2012r2.1 without any issues.
There is a Windows with methods that collects some information and has a method that is called from the progress bar window to do the actual importing.
Here is the code that calls the actual progress bar window.
CALL TO PROGRESS BAR WINDOW FROM wImportAssets
wPB_ExportImport.Startup(“Import from Excel”,“ImportExcel”)
Here is the code from the code from the Thread1.run method in the wPB_ExportImport window.
THREAD1.RUN METHOD
self.Timer1.Mode = 2
Select case Method2Call
case “ImportCSV”
wImportAssets.ImportCSV()
case “ImportExcel”
wImportAssets.ImportExcel()
case “UndoImport”
wImportAssets_Advanced.UnImportAssets()
case “ExportData”
wExportAssets.ExportData()
case “ExportDataRT” // REPORT TEMPLATE
wExportAssets.ExportDataRptTemplates()
case “DeleteExportedAssets”
wExportAssets.DeleteExportedAssets()
end
It’s just a case to call the appropriate method in the appropriate Window. In this case, it calls the method ImportExcel() in the wImportAssets window.
So in summary, the wImportAssets window calls the progress bar window which in turn calls the method that is in the wImportAssets window. This allows me to localize all the code for doing various procedures in the window that collects preliminary information and have a generic progress bar window that can be used for various procedures. Works great, till now.
[quote=84061:@John Fatte]There were a number of programmers at the office that looked at this, but here are some code snippets to show the routine. Keep in mind that this is the same code that was running in RB2012r2.1 without any issues.
[/quote]
2012r2.1 also didn’t warn you if you were accessing the UI from a thread and it just may have worked but not necessarily safely
In the bit you posed is wImportAssets a window ?
If so there’s at least one culprit as the thread calls
case "ImportCSV"
wImportAssets.ImportCSV()
which is a thread directly referring or trying to use something on the UI
The import will import from CSV or from Excel. Depending on what the options selected of course.
How do you figure that wImportAssets.ImportCSV() is a UI directive. It’s simply calling a method to do the CSV import.
BTW, there does seem to be something else going on because I just tested the Export to Excel and it worked without any problems. Progress bar and all. It has a window (wExportAssets) that calls the same progress bar window which calls a method in the wExportAssets window that exports the data to Excel. Works properly without any problems.
Without the actual code and only the name of the methods, impossible to figure what maybe wrong. But where are you importing the data to ? If you place it into a variable like an array, fine. But if you import to a listbox (most used for Excell data), then it is a UI control. To make it safe, you should first import to a variable, then update the listbox in the timer when import is complete.
Same kind of question : where is the data coming from ? As long as you do not read from a control, you are fine. If the data comes from an array, it will save without error.
Michael - Thanks for the reply. The data is being directly added to a database record, no UI controls involved.
I’ve got an idea that came to me last night that I’m going to try and see if that resolves it. I’m thinking that my Excel object is out of scope and that’s why it won’t read from the spreadsheet. It may be because of the following:
Form A - UI form to get data from user
Method A - does the conversion
Property A - Excel object
Calls Form B with instructions to call Method A
Form B - Progress bar
Timer A - calls Method A in Form A
The question is, when Method A is called from Form B is Property A (Excel object) still in scope?
[quote=84191:@John Fatte]Michael - Thanks for the reply. The data is being directly added to a database record, no UI controls involved.
I’ve got an idea that came to me last night that I’m going to try and see if that resolves it. I’m thinking that my Excel object is out of scope and that’s why it won’t read from the spreadsheet. It may be because of the following:
Form A - UI form to get data from user
Method A - does the conversion
Property A - Excel object
Calls Form B with instructions to call Method A
Form B - Progress bar
Timer A - calls Method A in Form A
The question is, when Method A is called from Form B is Property A (Excel object) still in scope?[/quote]
The error you are getting is not a problem of scope. It is a problem of the code in your thread calling the UI.
Unless you post or make available for download the code you got into your thread, it is no point shooting in the dark.
Sorry to disagree, but it is exactly what I thought it was. For some reason, the method called from the thread could not see the Excel object.
Here is what I did to fix it.
Before the call to the progress bar, I closed Excel. Here is the exact code.
'--- CLOSE EXCEL
BOOK.Close
EXCEL.Quit
EXCEL = NIL
Then at the top of the Method A where the conversion actually takes place, I simply added the following:
EXCEL = New ExcelApplication
book = Excel.Workbooks.Open(ImportFile.AbsolutePath)
I was trying to keep from opening the Excel file more than one time because in some cases this can be a lengthy process. I have one user that routinely imports 20,000 records. However, unless I re-open the excel file IN THE METHOD WHERE IT READS IT, then it simply barfs when it tries to read a cell.
Let me know if I’m not being clear.
BTW, there is only two lines in my ImportExcel method that read from Excel. They are as follows:
The first line checks to see if it is empty, and the next line reads the cell contents. It is then processed based on type of data after that.
if EXCEL.Range(cCell).Value <> “” then
cData = Excel.Range(cCell).Value
Create a global variable, and alter it within the thread, during reading the file (add 1 to the var, for example).
Place a timer on the importing window, let it read that variable and update the progress bar accordingly.