Vbscript - 获取 Excel 列索引的问题 [英] Vbscript - Issues in getting the Excel column Index

问题描述

我在创建一个简单的函数时遇到了一个问题，该函数根据列名返回 excel 列索引.我无法理解为什么脚本的行为如此怪异(请参阅代码注释以了解问题).我知道一定有一个愚蠢的错误，但我就是找不到它.非常感谢您的帮助！

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

function fn_findColumnIndex(sheetObj, strColumnName)Dim i, j, strTemp, intIndexj = sheetObj.usedRange.Columns.Count '稍后将用更好的方法替换它以获得列数.当我尝试打印值时，这现在很好用整数索引 = 0对于 i = 1 到 j 第 1 步strTemp = sheetObj.cells(1,j).value '定位第一行中的值(即列名)'msgbox strTemp '当我取消注释这一行时，第 1 行中的所有值都显示为空白.这就是函数总是返回 0 的原因.但为什么这些值是空的?如果 strComp(strTemp,strColumnName,1) = 0 那么intIndex = j退出万一下一个fn_findColumnIndex = intIndex结束函数

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

set objExcel = createObject("Excel.Application")objExcel.visible = falseobjExcel.displayAlerts = falsestrCDPath = "E:\Base\TestDesign\CycleDriver\CycleDriver.xlsx"设置 objBook = objExcel.WorkBooks.Open(strCDPath)set objSheet = objBook.WorkSheets("CycleDriver")intRunColumn = fn_findColumnIndex(objSheet,"Run") '在这里得到值 0.为什么?intModuleColumn = fn_findColumnIndex(objSheet,"Module") '在这里得到值 0.为什么?intTestScriptColumn = fn_findColumnIndex(objSheet,"TestScript") '在此处获取值 0.为什么?objBook.closeobjExcel.quit

这是工作表的样子:

解决方案

我不确定 vbscript 是否支持类似 vba 的 IsError 之类的东西，但如果保证列标题标签的存在，我会尝试这样的事情.

>

intRunColumn = objExcel.match("运行", objSheet.rows(1), 0)intModuleColumn = objExcel.match("模块", 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'应该，strTemp = sheetObj.cells(1, i).value'还intIndex = i

所以你总是在 UsedRange 的右端得到标题标签.

I am stuck on an issue while creating a simple function that returns the excel column index, given the column name. I am not able to understand why is the script is behaving so weirdly(please see code comments for issues). I know there must be a silly mistake but I am just not able to find it. Will greatly appreciate your help!

The following is the function which was supposed to return the index of the column in an excel sheet:

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

The following is the piece of code which is calling the above-mentioned 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

This is how the Sheet looks like:

解决方案

I'm not sure if vbscript supports something like vba's IsError but if the existence of the column header labels is guaranteed, I would try something like this.

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

Your own function was failing due to using i as the counter in,

for i = 1 to j step 1

... but using j to gather data like,

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

So you were always getting the header label at UsedRange's right end.

这篇关于Vbscript - 获取 Excel 列索引的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！