![列索引的问题]( "列索引的问题")
本文介绍了Vbscript - 获取 Excel 列索引的问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在创建一个简单的函数时遇到了一个问题，该函数根据列名返回 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 列索引的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！