adding excel columns 3

[LindaRichard]

Hi,

I have some code that adds columns to an excel sheet.
My problem is when I go behond the Z column, I get an error.
I know that Excel numbers form A...Z the AA AB etc...
My question is does anyone know of a different way of accessing each
column and avoiding writing a complex string building expression
Here is my code.

Thanks for your help
Linda

*----------------------------------------------------------------
*create excel object
*----------------------------------------------------------------
goExcel = CREATEOBJECT("Excel.application&quot
*----------------------------------------------------------------
*open a workbook
*----------------------------------------------------------------
WITH goExcel
*add workbook sheets (this adds 3 sheets by default)
.Application.Workbooks.Add
*delete the other 2 sheets don't need them
*turn the warning message off
.APPLICATION.DisplayAlerts = 0
*delete sheet2
.Worksheets("Sheet2&quot.DELETE
*delete sheet3
.Worksheets("Sheet3&quot.DELETE
*turn the warning message on again
.APPLICATION.DisplayAlerts = 1
*make the first sheet the active one
.Application.Worksheets("Sheet1&quot.Activate && Select sheet
giLineCount=1
*print the field titles
FOR t=1 TO giSelectedNumber
*print the field label
.RANGE(CHR(96+t)+ALLTRIM(STR(giLineCount))).VALUE=gaSelectedFieldName(t)
NEXT t
ENDWITH

 

[Mike Gagnon]

LindaRichard

Where in your code are you adding a column as the title of your thread suggests?
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[wgcs]

The string building routine isn't all that complicated:

PROC ExcelCol( pnCol )
LOCAL lnCol,lcCol
lnCol = pnCol
lcCol = ''
do while lnCol>0
  lcCol = chr(asc('A')+ (lnCol-1)%26 ) + lcCol
  lnCol = int( (lnCol-1) / 26 )
enddo
RETURN lcCol

 

[LindaRichard]

Thanks for your response,

I gave your code a try and it works great.

Linda

 

[rusu]

in place of

.RANGE(CHR(96+t)+ALLTRIM(STR(giLineCount))).VALUE=gaSelectedFieldName(t)

use

      .Cells(lnRowNumber,lnColumnNum).value=gaSelectedFieldName(t)

where lnRownumber,lncolumnNumber are numeric values

 

[LindaRichard]

rusu,

Thanks for the tip, the cells property works great.

Linda

 

[nirajj]

Linda,

One thing I realized is your assumption that adding a new workbook will always add 3 sheets. But you can actually set it between 1 to 255 if you want.
In Excel goto :
Tools -> Options -> General -> Sheets in new workbook

I was wondering if it was set something other than 3 sheets in new workbook will your code generate a error ? Probably you would like to check on it.

 

[nirajj]

BTW.. you can use "SheetsInNewWorkbook" property to check it.