We are in the process of converting an application written in RB2012r2.1 to the latest and greatest version of XOJO and just ran into an office automation bug. I will refer you to a post by Tim Hare regarding what you can expect when you read the contents of an Excel cell.
It appears that in XOJO this is no longer true. And this is a huge problem for our software, which contains an import feature that reads an Excel spreadsheet that can contain an unspecified number of columns and rows. We relied on the NIL return to determine if a cell had a value but this no longer works.
Anyone else run into this and what options there are to workaround. Tim, you out there?
Anyone who is using office automation has to pay very close attention to this bug.
Thanks for the reply; however, that really doesn’t help much because if you’re trying to determine the number of columns of data that are in a spreadsheet and some of them may be empty, then you wouldn’t know what to stop reading cells because a blank does not necessarily mean the end of the data.
Do you know of a way to issue the command that exists in Excel to go to the last column of data - I believe in Windows it’s CTRL-END.
Well, that’s good news and bad news. I’m going to have to retool my excel import stuff.
Eugene, would you be kind enough to post a snippet that would return what the last cell is? And the last row? I’m thinking something along the lines of
lastrow = get last row of data
lastcell = get last cell of data
for row = 1 to lastrow
for cell = 1 to lastcell
get the data
next
next
Does something like the following work? This code creates two columns with a space in between to try and mess up excel. A messagebox counts and shows the number of rows, the number of columns, and the data in the last (bottom right) cell. Is this what you are looking for? Feel free to correct me
[code] Dim excel as new ExcelApplication
excel.Visible = True
excel.Workbooks.add
'Create some data with a blank column
Dim i as Integer
For i = 1 to 30
excel.Range(“A”+Cstr(i)).Value = “test”
excel.Range(“C”+Cstr(i)).Value = CStr(i)
Next i
'Show rows and columns
MsgBox "There are " + excel.Range(“A65536”).End_(Office.xlUp).Row +Chr(13) + "Rows " +_
“and " + excel.ActiveSheet.cells(2, excel.Columns.Count).end_(Office.xlToLeft).column + " Columns.” + chr(13) +_
"and the last cell value is " + excel.ActiveCell.SpecialCells(Office.xlLastCell).Value
excel = nil
Exception err as OLEException
MsgBox err.message
[/code]
Yes, Eugene, your example gives me what I’m looking for. Unfortunately, when I tried it on an existing spreadsheet, it gets the right number of rows, but the column count one less than it should be.
Ah, I see the problem. The second row of my spreadsheet didn’t have a value in the last column. I can use row 1, which will always have headers. Or I could loop through all the rows and find the max column count.
Hmm, I can’t seem to get an incorrect column count. One possible reason is the excel.activesheet.cells first value should be 1 and not 2. Try this code, and if it doesn’t work, I will create a different way to count the columns. Here is the corrected code:
[code] Dim excel as new ExcelApplication
excel.Visible = True
excel.Workbooks.add
'Create some data with a blank column
Dim i as Integer
For i = 1 to 30
excel.Range(“A”+Cstr(i)).Value = “test”
excel.Range(“C”+Cstr(i)).Value = CStr(i)
Next i
'Show rows and columns
MsgBox "There are " + excel.Range(“A65536”).End_(Office.xlUp).Row +Chr(13) + "Rows " +_
“and " + excel.ActiveSheet.cells(1, excel.Columns.Count).end_(Office.xlToLeft).column + " Columns.” + chr(13) +_
"and the last cell value is " + excel.ActiveCell.SpecialCells(Office.xlLastCell).Value
excel = nil
Exception err as OLEException
MsgBox err.message[/code]
[code] Dim excel as new ExcelApplication
excel.Visible = True
excel.Workbooks.add
'Create some data with a blank column
Dim i as Integer
For i = 1 to 30
excel.Range(“A”+Cstr(i)).Value = “test”
excel.Range(“C”+Cstr(i)).Value = CStr(i)
Next i
'Show rows and columns
MsgBox "There are " + excel.Range(“A65536”).End_(Office.xlUp).Row +Chr(13) + "Rows " +_
“and " + excel.Range(“A65536”, excel.ActiveSheet.UsedRange).columns.count + " Columns.” + chr(13) +_
"and the last cell value is " + excel.ActiveCell.SpecialCells(Office.xlLastCell).Value
excel = nil
Exception err as OLEException
MsgBox err.message
[/code]
Another possible thought is to try the older rbx plugin and use it in the latest version of Xojo by placing it in the plugins folder. I don’t know if it will cause any stability issues, and it seems like Xojo is keeping the rbx extension, so it might be an option.
How many rows can your Excel spreadsheets have? Modern versions of Excel can have more than 65536 rows. Excel tells me that the correct value is 1048566. I think this is office 2013 here because it looks so ugly.
Eugene, just wanted to thank you for the code. It’s exactly what I am looking for. Tim, thanks for chiming in. Good to know I’m not the only one that ran into this problem. I’m sure there are others who just don’t realize it yet.
I’ve also got some code that uses the CountA function to get the number of non-blank rows, then I go to the last row/column and use a loop to increase the number returned in case there were blanks in the row/column. That seems to work well also. Be happy to share the code if anyone needs it.
The number of columns in Excel 97, 2000, and 2003 are 256 which is the alphabetical column IV. There are 65,536 rows for a total of 16,777,216 cells.
The number of columns in Excel 2007, 2010, and 2013 are 16,384 that goes to the alphabetical column XFD. There are 1,048,576 rows for a total of 17,179,869,184 cells.
In the code I used 65536 which would work for most Excel applications and the row value can be changed to 1,048,576 for Excel projects with very much data. If there is enough data in a spreadsheet that the larger number of cells are required, I would suggest using a database program such as Microsoft Access, Xojo SQlite, or PostGRE SQL. Access is good for those people/companies who have a local database on their computer and typically stay with the Microsoft OS. SQLite is a good local databaase for all of the OS’s Xojo supports, and PostGRE SQL is an industrial sized database for multiple users, heavy workloads, extreme stability, and industrial workloads for the price of zero MySQL can also be used, and the legal document is getting long for MySQL, making PostGRE SQL the preferred database which works on multiple OS’s.
