Find last cell on Excel workbook 2

[AndrewMozley]

Trying to find the address (e.g. “Z99”) of the last cell on an Excel spread sheet. "Microsoft Office Automation" recommends (after I have created the Excel object as Thisform.zExcel) :
.zExcel.Cells.specialCells(11).Row

And this certainly works on my Excel 2003 files.

But it does not work on my client’s files (which may be Excel 2007). and the above statement gives the error Member SPECIALCELLS does not evaluate to an object
The problem persists even if I open the file with Excel 2003, save it as a Microsoft Excel Workbook and try the above code again.

I have tried to find out what the correct reference is, using Intellisense to locate likely properties.
What I have noticed is that once I have navigated to (say) 3 levels (e.g. AMTest1 = .zExcel.cells or AMTest2 = .zWorkbook.cells) . . . there are a very large number of properties offered by Intellisense and most of these are the same at all levels; very few return a useful value. Indeed they often say “xxx cannot be evaluated”.

 

[cbasoz]

SpecialCells work in 2007 too.

I would instead use UsedRange:

oExcel.Activeworkbook.activesheet.UsedRange.Rows.Count

Cetin Basoz
MS Foxpro MVP, MCP

 

[JRB-Bldr]

I have not tried using the following in Excel 2007, but in Excel 2000/2003 it has worked well for me.

* --- Determine Last Cell At This Point ---
nLastRow = oExcel.activesheet.UsedRange.ROWS.COUNT
nLastCol = oExcel.activesheet.UsedRange.COLUMNS.COUNT

Good Luck,
JRB-Bldr

 

[AndrewMozley]

Thank you Cetin and JRB. Both of these statements worked.
regards. Andrew Mozley