Numbering Excel Columns

General Macro Scheduler discussion

Moderators: Dorian (MJT support), JRL

Post Reply
User avatar
JRL
Automation Wizard
Posts: 3497
Joined: Mon Jan 10, 2005 6:22 pm
Location: Iowa

Numbering Excel Columns

Post by JRL » Fri May 04, 2012 5:48 pm

I often use ODBC to retrieve data from excel files.

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
      /////////////////////////////////////////////////////////////////////
The results are returned in an array of variables that reflect the row and column locations of the data. For example the data found in row 5:column G will reside as the value of variable "Excel_Cell_5_7". Notice that the row number "5" is part of the variable name, also notice that there is no column label "G" in the variable name. You have to know that "G" is the seventh letter of the alphabet so the data in column "G" will always reside in a variable with "_7" at the end.

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

hagchr
Automation Wizard
Posts: 327
Joined: Mon Jul 05, 2010 7:53 am
Location: Stockholm, Sweden

Re: Numbering Excel Columns

Post by hagchr » Sun May 22, 2016 9:45 am

Maybe cheating, but if you have excel available you could use it to get the column number.

Code: Select all

VBSTART
Dim col
Sub GetCol(cell)
    Set objExcel = CreateObject("Excel.Application")
    objExcel.visible=False
    
    Set Workbook=objExcel.workbooks.add
    Workbook.worksheets("sheet1").range("a1").value="=COLUMN(" & cell & ")"

    col=objExcel.worksheets("sheet1").range("a1").value

    Workbook.close False
    objExcel.Quit
    Set objExcel = Nothing
End Sub
VBEND

VBRun>GetCol,gx1
VBEval>col,col
MDL>col

Post Reply
Sign up to our newsletter for free automation tips, tricks & discounts