Code: Select all
Let>DataFile=C:\MyFile.xls
Let>SheetName=Sheet1
////////////////////////// Excel Connect //////////////////////////
Let>connStr=Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%DataFile%;Extended Properties=Excel 8.0;
DBConnect>connStr,dbH
Let>SQL=select * from [%SheetName%$]
DBQuery>dbH,SQL,Excel_Cell,Excel_nR,Excel_nF
DBClose>dbH
/////////////////////////////////////////////////////////////////////
This system is easy to work with as long as you only have a few columns of data. But what is the number of the Excel column labeled "GX". Not so intuitive.
So I have written a converter. you can use this to just view the column numbers using the editor watch list or you can use it in your code to transpose as you go.
There is probably an easier way to do this, if you have a better method, feel free to share.
Code: Select all
//Usage:
//GoSub>ExcelColumnTransposition,[1] ("1" for column_nbr=char)(nothing for column_char=nbr)
GoSub>ExcelColumnTransposition
//GoSub>ExcelColumnTransposition,1
**BREAKPOINT**
SRT>ExcelColumnTransposition
VBSTART
VBEND
Let>Excel_Col_Adder=64
Let>Excel_Col_chr_counter=64
Repeat>Excel_Col_chr_counter
Add>Excel_Col_chr_counter,1
VBEval>chr(%Excel_Col_chr_counter%),Excel_col_trans_res1
Let>Excel_Col_Nbr={%Excel_Col_chr_counter%-64}
If>ExcelColumnTransposition_var_1=1
Let>Excel_Column_%Excel_Col_Nbr%=Excel_col_trans_res1
Else
Let>Excel_Column_%Excel_col_trans_res1%=Excel_Col_Nbr
EndIf
Until>Excel_Col_chr_counter=90
Repeat>Excel_Col_Nbr
Add>Excel_Col_Adder,1
VBEval>chr(%Excel_Col_Adder%),Excel_col_trans_res2
Let>Excel_Col_char_counter=64
Repeat>Excel_Col_char_counter
Add>Excel_Col_char_counter,1
VBEval>chr(%Excel_Col_char_counter%),Excel_col_trans_res1
Add>Excel_Col_Nbr,1
If>ExcelColumnTransposition_var_1=1
Let>Excel_Column_%Excel_Col_Nbr%=%Excel_col_trans_res2%%Excel_col_trans_res1%
Else
Let>Excel_Column_%Excel_col_trans_res2%%Excel_col_trans_res1%=%Excel_Col_Nbr%
EndIf
If>Excel_Col_Nbr=256
Goto>ExcelColumnNumberingDone
EndIf
Until>Excel_Col_char_counter=90
Label>ExcelColumnNumberingDone
Until>Excel_Col_Nbr=256
END>ExcelColumnTransposition