Excel - Convert Column Number to Column Reference

Submitted by Sergey on January 1, 2008 - 20:29

This is sample code. Add error handling and adjust to your requirements as necessary.

#DEFINE xlA1	1	
#DEFINE xlR1C1	-4150	
 
lnCol = 123
* Using Excel
* oSheet - active sheet in a workbook
lcColRef = oSheet.Cells(1,lnCol).Address(.T.,.F.,xlA1)
*lcColLetter = LEFT(lcColRef, AT("$", lcColRef)-1)
lcColLetter = STREXTRACT(lcColRef, "", "$")
 
* VFP code only
lcColLetter = IIF(lnCol>26, CHR(Int((lnCol - 1) / 26) + 64), "") + CHR(((lnCol - 1) % 26) + 65)
No votes yet