代码之家 › 专栏 › 技术社区 › Gurmanjot Singh

vbscript-获取excel列索引的问题

vbscript excel

0

Gurmanjot Singh · 技术社区 · 7 年前

我在创建一个简单函数时遇到了一个问题,该函数在给定列名的情况下返回excel列索引。我无法理解为什么脚本的行为如此怪异(请参见代码注释了解问题)。我知道一定有个愚蠢的错误,但我就是找不到。非常感谢你的帮助!

下面是应该在Excel表中返回列索引的函数:

function fn_findColumnIndex(sheetObj, strColumnName)
    Dim i, j, strTemp, intIndex
    j = sheetObj.usedRange.Columns.Count        'will replace it later with better ways to get column count. This works fine for now when I tried to print the value
    intIndex = 0
    for i = 1 to j step 1
        strTemp = sheetObj.cells(1,j).value     'Targeting the values in the 1st row(i.e, the column names)
        'msgbox strTemp                         'when I uncomment this line, all the values in row 1 are displayed as blank. That's the reason the function always return 0. But why are these values blank?
        if strComp(strTemp,strColumnName,1) = 0 then
            intIndex = j
            Exit For
        end if
    next
    fn_findColumnIndex = intIndex
end function

下面是调用上述函数的代码:

set objExcel = createObject("Excel.Application")
objExcel.visible = false
objExcel.displayAlerts = false
strCDPath = "E:\Base\TestDesign\CycleDriver\CycleDriver.xlsx"
Set objBook = objExcel.WorkBooks.Open(strCDPath)
set objSheet = objBook.WorkSheets("CycleDriver")
intRunColumn = fn_findColumnIndex(objSheet,"Run")               'getting the value 0 here. Why?
intModuleColumn = fn_findColumnIndex(objSheet,"Module")         'getting the value 0 here. Why?
intTestScriptColumn = fn_findColumnIndex(objSheet,"TestScript") 'getting the value 0 here. Why?
objBook.close
objExcel.quit

这就是这张纸的样子:

1 回复 | 直到 7 年前

1

2

user4039065 7 年前

我不确定vbscript是否支持类似vba的iserror,但如果列标题标签的存在是有保证的,我会尝试这样做。

intRunColumn = objExcel.match("Run", objSheet.rows(1), 0)
intModuleColumn = objExcel.match("Module", objSheet.rows(1), 0)
intTestScriptColumn = objExcel.match("TestScript", objSheet.rows(1), 0)

由于使用I作为计数器,您自己的函数失败了,

for i = 1 to j step 1

…但是用J来收集数据,比如,

strTemp = sheetObj.cells(1, j).value
'should be,
strTemp = sheetObj.cells(1, i).value
'also
intIndex = i

所以你总是在Usedrange的右端得到标题标签。