vbs读取excel的一个实例

功能：在excel中对ip与loginType这两列进行遍历读取。本程序依赖于excel文件的"sheet2"表单中具有这两列。

dim dictTarget, objExcel
'从交换机台账中提取ip/loginType数据
getDictTarget
objExcel.Quit

'Msgbox dictTarget.Count
'遍历所有ip,进行登录
keySet = dictTarget.keys
for i = 0 to dictTarget.Count -1
	ip = keySet(i)
	loginType = dictTarget.Item(ip)
	Msgbox ip&" : "&loginType
next

function getDictTarget
'功能：打开excel并将"ip","登录方式"这两列值提取为dictionary
	Set dictTarget = CreateObject("Scripting.Dictionary")
	Set objExcel = CreateObject("Excel.Application")
	Set objWorkbook = objExcel.Workbooks.Open("D:\桌面\Desktop\1.xlsx")
	objExcel.WorkSheets("Sheet2").Activate
	intColOfIP = getColNumInRowByValue(1, "IP")
	'Msgbox "intColOfIP : "&intColOfIP
	intColOfLoginType = getColNumInRowByValue(1, "登录方式")
	'Msgbox "intColOfLoginType : "&intColOfLoginType
	
	intRow = 2
	Do Until objExcel.Cells(intRow, intColOfIP).Value = ""
		ip = objExcel.Cells(intRow, 1).Value
		loginType = objExcel.Cells(intRow, 2).Value
		dictTarget.add ip, loginType
	intRow = intRow + 1
	Loop
end function

function getColNumInRowByValue(intRow, strValue)
'获取指定行中指定内容的单元格的列值
	intCol = 1
	intResult = -1
	value = "tmp"
	Do while value <> ""
		value = objExcel.Cells(intRow, intCol).Value
		if value = strValue then
			intResult = intCol
			exit do
		end if
		intCol = intCol + 1		
	Loop
	getColNumInRowByValue = intResult
	'Msgbox "intResult : "&intResult
end function

问题：在win7中测试一切正常。然而在xp中测试发现，运行结束后任务管理器中会残留一个EXCEL.EXE无法退出，即使已经调用了“objExcel.Quit”语句。

转载于:https://www.cnblogs.com/pzy4447/p/3726424.html