I have a script which will be searching a spreadsheet that is not always formatted the same way, but always contains certain column headings. I need to have access to the column letter rather than the column index. Is there an easier way to find that the column titled “someTitle” is in Column “C” rather than Column 3, rather than what I have found here?
tell application "Microsoft Excel"
set t_index to first column index of (find row 1 what "someTitle" look in values look at whole)
set theAddress to get address of (the second row of column t_index)
set theColumn to the second text item of theAddress
end tell
return theColumn
Something like
set columnLetter to "C"
tell application "Microsoft Excel"
set myColumn to range (columnLetter & ":" & columnLetter)
end tell
No, I know how to select a column or even a range once I know the value for the column. The problem is finding that value. The column with the heading “someTitle” could be C in spreadsheet X, or it could be AF in spreadsheet Y.
When you search for “someTitle”, Excel Returns the Column Index Number. So, if it is in column C, Excel is returning “3”
OH, the other way 'round:
tell application "Microsoft Excel"
set myColumn to cells of column 3
set theAddress to get address of myColumn
if character 3 of theAddress is contained in { "1", "2", "3", "4", "5", "6", "7", "8", "9", "0"} then
set columnAddress to character 2 of theAddress
else
set columnAddress to text 2 thru 3 of theAddress
end if
end tell
Which is pretty much what I did in my first example… except what is not shown is that I was setting text item delimiters to “$” - and grabbing the second text item in order to account for column “A” or “AA.”
So, it appears that my method is about the most direct way to acquiring the info.
If you are sure that the column is to the left of AA, you could use
ColumnLetter = ASCII character (ColumnNumber+64)
It also occurs to me that finding column numbers or addresses may be unnessesary. Range objects have a variety of tools for being maipulated without dealing with their addresses.
tell application "Microsoft Excel"
set columnOfTitles to entire column of (find row 1 what "someTitle" look in values look at whole)
end tell
I use the get resize, get offset and get end commands often when handling ranges.