how to get excel col. letter

  • Thread starter Thread starter Marc Miller
  • Start date Start date
M

Marc Miller

This is my first attempt working with Excel from vb.net. Whew!

I'm trying to get the column letter so that I can create a range variable.

1. I am looping thru the sheets using :
for i = 1 to objExcel.Sheets.Count()

2. Then I want to loop thru the columns using:
for iCol = 1 to objExcel.activesheet.columns.count()

This is where I need the range variable so that I can issue something like:

objExcel.Range(cRange).Select()


Does anyone understand this? I'm probably not being clear enough,
but if you do understand this, can you help?

My project is to loop thru all cells in a workbook to find links to servers.
We are
migrating from Novell to MSFT and our server names are going change and I
need
to update our corp.'s spreadsheets.


Thanks Mucho!
Marc Miller
 
Marc said:
This is my first attempt working with Excel from vb.net. Whew!

I'm trying to get the column letter so that I can create a range variable.

1. I am looping thru the sheets using :
for i = 1 to objExcel.Sheets.Count()

2. Then I want to loop thru the columns using:
for iCol = 1 to objExcel.activesheet.columns.count()

This is where I need the range variable so that I can issue something like:

objExcel.Range(cRange).Select()
Click to expand...
Excel column and row references are numeric. If you need the letter,
you'll have to do the conversion. But for a range you'll want the
column number anyway.

T
 
Hello Marc,

As tomb suggests, you may not actually need the column "letter". There
are other ways to get the ranges you need. If you really do want the
letter, you can get it thus:

Dim TopCellAddr As String = xlSheet.Cells(1, iCol).Address
Dim ColumnLetter As String
ColumnLetter = TopCellAddr.Substring(1, TopCellAddr.Length - 3)

where xlSheet is the Worksheet that you are working with (e.g.
"objExcel.activesheet")

But you probably don't want to iterate through ALL columns (and rows).
It could take a long time to scan a lot of empty cells. Consider
iterating through the cells (or columns/rows) in xlSheet.UsedRange instead.

Also, unless you're actually interacting with a user, it is usually not
necessary (or even desirable) to activate/select the workbooks, sheets
and cells that you are working with. Just work with the objects
directly (assigning them to a variable of the appropriate type where
this is beneficial).

Cheers,
Randy
 
Back
Top