Excel - Convert Column Number to Column Reference

Submitted by Sergey on January 2, 2008 - 00:29 UTC
topic: 
excel
code
vfp
automation

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





 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15





#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)

Comments