Get Excel-Column from Range

[Ahmed Martens]

Hallo Folks,

i have following Code to get the Number from a Range.Column:



Dim iCol
dim objExcel

Set objExcel = GetObject(,"Excel.Application")

iCol = GetSpaltenNummer(objExcel.Range("R14"))

Function GetSpaltenNummer(rngCell)
GetSpaltenNummer= objExcel.rngCell.Column
End Function

Excel ist running and in VBSEdit i can see, that the number from the
Column is = 18. But the retunvalue is every time = empty.

What is wrong.
Can anyone help me?

Thanks of all.

Gruß Ahmed

[Evertjan.]

Post by Ahmed Martens
Hallo Folks,
Dim iCol
dim objExcel
Set objExcel = GetObject(,"Excel.Application")
iCol = GetSpaltenNummer(objExcel.Range("R14"))
Function GetSpaltenNummer(rngCell)
GetSpaltenNummer= objExcel.rngCell.Column
End Function
Excel ist running and in VBSEdit i can see, that the number from the
Column is = 18. But the retunvalue is every time = empty.
What is wrong.

Methinks this is VBA, not VBS.

This NG is about VBS.

Post by Ahmed Martens
Can anyone help me?
Thanks of all.
Gruß Ahmed

[Ahmed Martens]

Halla Evertjan,

this code was created by VBA, but i have converted to VBS.

Here are 2 Function (VB-Code -> Converted to VBS):
It works very fine.

'http://www.vbarchiv.net/tipps/tipp_190-umrechnung-zahl-in-excelspalte-und-umgekehrt.html

WScript.Echo GetExcelCol(50,False)
'Result => AX

Public Function GetExcelCol(lIdx, bInitialCall) 'as Sring
If (bInitialCall) Then lIdx = lIdx + 1
If (lIdx = 0) Then Exit Function
GetExcelCol = GetExcelCol((lIdx - 1) \ 26, False) + Chr(65 + (lIdx -
1) Mod 26)
End Function


WScript.Echo GetIndexOfExcelCol("AX", 0,False)
'Result => 50

Public Function GetIndexOfExcelCol(ByVal strCol, ByVal slevel, ByVal
bInitialCall) 'as Long
Dim tInitialCall
If (CInt(slevel) = Len(strCol)) Then Exit Function

If CBool(bInitialCall) = True Then
tInitialCall=-1
Else
tInitialCall=0
End if
GetIndexOfExcelCol = GetIndexOfExcelCol(strCol,CInt(slevel) + 1,
False) + ((Asc(Mid(strCol, Len(strCol) - CInt(slevel), 1)) - 65) + 1) *
26 ^ CInt(slevel) + CLng(tInitialCall)

End Function

Gruß Ahmed

[Evertjan.]

Post by Ahmed Martens
Halla Evertjan,
this code was created by VBA, but i have converted to VBS.

VB is not VBA is not VBS, methinks.

Post by Ahmed Martens
It works very fine.

If it works fine, as you say, what is your Q?

Methinks you do not supply the excel-file and path,
nor the table/worksheet in that file,
so how is your code to know where to look for the field?

Post by Ahmed Martens
'http://www.vbarchiv.net/tipps/tipp_190-umrechnung-zahl-in-excelspalte-un
d-umgekehrt.html
WScript.Echo GetExcelCol(50,False)
'Result => AX
Public Function GetExcelCol(lIdx, bInitialCall) 'as Sring
If (bInitialCall) Then lIdx = lIdx + 1
If (lIdx = 0) Then Exit Function
GetExcelCol = GetExcelCol((lIdx - 1) \ 26, False) + Chr(65 + (lIdx -
1) Mod 26)
End Function
WScript.Echo GetIndexOfExcelCol("AX", 0,False)
'Result => 50
Public Function GetIndexOfExcelCol(ByVal strCol, ByVal slevel, ByVal
bInitialCall) 'as Long
Dim tInitialCall
If (CInt(slevel) = Len(strCol)) Then Exit Function
If CBool(bInitialCall) = True Then
tInitialCall=-1
Else
tInitialCall=0
End if
GetIndexOfExcelCol = GetIndexOfExcelCol(strCol,CInt(slevel) + 1,
False) + ((Asc(Mid(strCol, Len(strCol) - CInt(slevel), 1)) - 65) + 1) *
26 ^ CInt(slevel) + CLng(tInitialCall)
End Function
Gruß Ahmed

[GS]

Try...

iCol = objExcel.ActiveSheet.Range("R14").Column