Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 02-10-2005, 09:08 AM   #1
aziz rasul
Newly Registered User
aziz rasul's Avatar
Join Date: Jun 2000
Location: Earth
Posts: 1,736
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Referencing a Cell

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?


Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Old 02-10-2005, 10:36 AM   #2
Registered User
Join Date: Aug 2002
Location: Raleigh, NC USA
Posts: 871
Thanks: 0
Thanked 1 Time in 1 Post
If you create a variable to equal the worksheet you're using such as xSheet then your line would look something like this:

Rob.Mills is offline   Reply With Quote
Old 02-10-2005, 10:53 AM   #3
Happy Retired Curmudgeon
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,341
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
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(n), 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(n) 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).Cel l(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.

The_Doc_Man is offline   Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 08:23 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World