R1C1 reference format when exporting to xlsx?
Posted: Sat Apr 06, 2013 5:08 am
Would it be possible to add support for the R1C1 cell reference format when exporting to Excel files (.xls, .xlsx and .xlsm)? If this feature was available these two lines would give the same result.
It would save users the hassle of having to create things such as the subroutine below in case they need to loop exports.
P.S. The method in the subroutine comes from this Stack Overflow post.
Code: Select all
pagesave(t=excelxml) "k:\stab\test.xlsx" range="Sheet1!A1"
pagesave(t=excelxml) "k:\stab\test.xlsx" range="Sheet1!R1C1"Code: Select all
subroutine GetExcelColumnName(scalar !v, string %name)
if @floor(!v)<>!v then
@uiprompt("The parameter !v ("+@str(!v)+") must be an integer.")
stop
endif
if !v<1 or !v>16384 then
@uiprompt("The parameter !v ("+@str(!v)+") cannot be outside the range 1-16384.")
stop
endif
%alphabet="A B C D E F G H I J K L M N O P Q R S T U V W X Y Z"
!dividend=!v
%columnName=""
while !dividend>0
!modulo=@mod((!dividend-1),@wcount(%alphabet))
%columnName=@word(%alphabet,!modulo+1)+%columnName
!dividend=@floor((!dividend-!modulo)/@wcount(%alphabet))
wend
string {%name}=%columnName
endsub