Referencing a Cell

[aziz rasul]

How do I reference a single cell in a xls file if I have 2 variables that contain the number of rows and columns respectively?

 

[Rob.Mills]

If you create a variable to equal the worksheet you're using such as xSheet then your line would look something like this:

xSheet.Cells(Row,Column)

 

[The_Doc_Man]

First, the help path is to go into Excel Help, find the VBA reference, and then examine the collections described therein.

But here is the not-so-short answer...

When you open Excel from Access, you do so as an Application Object. (It's in the help files under that name.)

When you have an Excel object, it has a collection of workbooks (documents) which (normally) is empty. But you can do an OpenWorkBook (I think) method to open a workbook. The Excel Help files adequately describes this collection and its methods. You'll know the method that opens a workbook when you see it. You can have more than one workbook open at once. If so, the numbered index/collection syntax works fine... Workbooks, for example.

When you open a workbook, it has a collection of worksheets. By default when Office has the settings with which it was shipped, Excel creates 3 sheets in a "virgin" workbook when you create a new workbook. Once the workbook is open, there is a shortcut to the selected sheet (ActiveSheet, I think - look this up in Excel help to find out more about it.) But as a collection, Worksheets works fine.

When you have selected a sheet, it has TWO collections: Rows and Columns. Both, as collections, support the collection/index syntax: Rows(1) or Columns(5).

In the collection of Rows, the columns are Cells.
In the collection of Columns, the ROWS are Cells.

For either Rows or Columns, Cells is the collection of ... well, ... cells in the row or column. Sorta like you might have guessed.

Cells include things like formatting information and the text you want. In the VBA Help for EXCEL (not Access), look at the contents of a Cell object. I think you want either .Text or .Range.Select.Text to extract the text from a cell. (I always get those confused with Word objects and paragraphs, so REALLY look at the help files first.)

So your problem was that you had row # and col # that you wanted to access directly. That's not enough. You need worksheet number, too. But let's say that you wanted to access Row 3, Column 4 of Sheet 2 of the first workbook you opened in your application object. That would be

Dim ceMyCell as Excel.Cell

Set ceMyCell = ExcelObj.Workbooks(1).Worksheets(2).Rows(3).Cell(4)

Then you could extract the formatting, color, text, etc. from the cell through the cell's properties.

The opposite direction of selection would be

Set ceMyCell = ExcelObj.Workbooks(1).Worksheets(2).Columns(4).Cell(3)

which is the same cell as the first reference - unless you had done some tricky cell merging so that some rows or columns have different numbers of components than others.