Thursday, December 4, 2008

Fun With Excel Visual Basic: Reading A Cell

The other day I desperately wanted to figure out how to read an Excel spreadsheet cell with Visual Basic for Excel. Seemed simple enough but no matter where I looked, all the answers to this rather simple problem were about 10 lines long, minimum.

Grr... programmers.

FINALLY, after a few days of searching, emailing and calling, I came across someone who answered my question. That is, once I got the right question.

If you want to read a cell from an Excel spreadsheet, using Visual Basic into a variable for later use in a macro / Visual Basic script, all you need to do is the following:
  • var = WorkSheets("Sheet 1").Cells(row,col)
Sheet 1 would be the name of the sheet at the bottom of the page,
Cells(row,col) is the number of rows down, columns to the right, starting your count from 1.
For instance, cell F3 would be Cells(3,6).

I'm not treating it like a database, I'm not doing fancy - I am just reading the cell for latter use in the routine.

Disclaimer: This works for the Excel file that contains both the sheet with the cells and the VB script in the same file. It does not account for reading a completely different worksheet from a different file, but I suspect this tidbit will get you going on the right path.

Enjoy. I finally did!

No comments:

Post a Comment

Hi - sorry for the confirmation but I need to weed out the noise from the well intended comments. Thanks for leaving a note... - Bruce