Making Sheet 1 the current sheet in an Excel Workbook 2

[AndrewMozley]

I need to know how many rows there are in Sheet 1 of an XL workbook that I have opened. The best function that I have found is :
oExcel.Cells.SpecialCells(11).row

However, if the user has a second sheet in the workbook (in which I am not interested), and he had saved the workbook so that that is the current page, then the above function returns the number of rows on the second page, and I then mistakenly only work on a subset of the rows on sheet 1.

I also notice that if Sheet 2 had been saved as the user’s current sheet, then after my program has finished and populated the cells of sheet 1, when the user opens it using Excel, he still starts on Sheet 2, but can then click on sheet 1 to see all the stuff I have generated.

So my question is “How can I make Sheet 1 the current sheet, so that the SpecialCells(11) function works off that sheet, and so that when the user opens the workbook, that is the sheet he sees”

 

[Mike Lewis]

Andrew,

You need to do something like this:

oWorkbook.Sheets("ABC").Select

where ABC is the name of the sheet in question.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[bborissov]

Or if you don't know the sheet name, but its index:

lnIndexOfSheet = 1 && or whatever value you want
oWorkbook.Sheets(lnIndexOfSheet).Select

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[TamarGranor]

The UsedRange property creates a range containing the rectangle of used cells.

Tamar

 

[AndrewMozley]

Thank you Mike, Boris, Tamar. I have used the oWorkbook.sheets(1).select statement and it does the trick.

Was not sure what your reference to the UsedRange property was referring to. Is this a property of the Excel object? Do you have an example of how it might be used?

As you know the Excel interface to VFP is not really documented by Microsoft; I certainly have not seen a user's guide with worked examples. Your own book has however been very useful to me, so thank you; it has started me in the right direction and I appreciate that.

 

[white605]

Your own book has however been very useful to me, so thank you; it has started me in the right direction and I appreciate that."

Thanks here also!

 

[TamarGranor]

UsedRange is a Worksheet property:

oRange = oXL.ActiveSheet.UsedRange

Glad the book has been helpful. Don't forget to take advantage of the VBA Help file for Excel.

Tamar