VBS遍历Excel工作表的实现代码_VBS

VBS遍历Excel工作表的实现代码

2020-08-20 10:47VBS代码网 VBS

这篇文章主要介绍了VBS遍历Excel工作表的实现代码,需要的朋友可以参考下

核心代码

									'******************************************

									'拖拽文件，获取文件路径

									'******************************************

									If wscript.Arguments.count=0 then 

									        msgbox "拖拽文件到本图标",0,"提示"

									End if 

									for a=0 to wscript.Arguments.count-1

									    strPath=wscript.Arguments(a)    

									next

									'******************************************

									'定义Excle对象、工作薄对象、工作表对象

									'******************************************

									dim oExcel,oWb,oSheet

									set ws=WScript.createobject("wscript.shell")

									Set oExcel=CreateObject("Excel.Application")

									'打开指定的工作簿

									Set oWb=oExcel.Workbooks.Open(strPath)

									'显示打开的Excel工作簿

									oExcel.visible=true

									'******************************************

									'遍历工作簿的所有工作表

									'******************************************

									for j= 1 to oWb.sheets.count

									    set oSheet=oWb.Sheets(j)

									    '选中并激活工作表

									    oSheet.Activate

									    oSheet.Range("A1")="成功"

									next

Excel遍历所有工作簿中所有工作表执行宏

									Sub test()

									n = Worksheets.Count

									For i = 1 To n

									Worksheets(i).Activate

									Macro1

									Next

									End Sub

Macro1是宏的名称

使用VBS遍历EXCEL

									Dim xlApp,xlSheet,xlWorkBookDim iRowCount,iLoop,jLoop,jColumnCount,numAdd

									Set xlApp=CreateObject("Excel.Application")

									xlApp.Visible=True

									Set xlWorkBook=xlApp.Workbooks.Open("C:\data.xls")

									Set xlSheet=xlWorkBook.Sheets("Sheet1")

									iRowCount=xlSheet.UsedRange.Rows.Count

									jColumnCount=xlSheet.UsedRange.Columns.Count

									For iLoop=1 To iRowCount

									 For jLoop=1 To jColumnCount

									 MsgBox(xlSheet.cells(iLoop,jLoop).value)

									 Next

									Next

									xlWorkBook.Save

									xlWorkBook.Close

									xlApp.Quit

VBScript 编写自动Excel文件内容到数组并提示输出

解压到任意目录，点击VBS文件执行，程序自动读取文件所在目录的Excel文件到数组中，并通过提示框逐个输出，提示框1s自动关闭。

									Dim oExcel,oWb,oSheet 

									Set oExcel= CreateObject("Excel.Application") 

									Set oWb = oExcel.Workbooks.Open(dir&"\DataReport.xls") 

									Set oSheet = oWb.Sheets("HistoryData")      

									Dim i

									Dim a(150)

									For i = 5 To 145 '145-5+1 = 141 

									a(i-5) = oSheet.Range("B"&i).Value

									print "data=",a(i-5)

									next

									Set oSheet = Nothing

									oExcel.Workbooks.Close

									oExcel.Quit '关闭excel.exe'

									Function Dir()

									Set WshShell = CreateObject("Wscript.Shell")

									Dir = WshShell.CurrentDirectory

									End Function

									Function print (prompt,title)

									Set WshShell = CreateObject("Wscript.Shell")

									WshShell.Popup prompt &title,1,""

									End Function