有时候我们需要批处理一些Excel文件,Vbscript可以帮助我们用简单的程序实现之。Vbscript脚本保存为.vbs文件,则可以双击运行,非常方便。
这个是示例代码,请保存为 run.vbs 文件:
' on error resume next
' define params:
dim ExcelApp
dim objWorkBook
dim objImportSheet
dim path
path = "C:/Users/Administrator/Desktop"
' open excel:
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
Set objWorkBook = ExcelApp.Workbooks.Open(path & "/1.xls")
Set xlsSheet = objWorkBook.Sheets("Sheet1") '页数名称
' read datas:
msgbox "第一行第一列数值: " & xlsSheet.Cells(1,1).Value
msgbox "第二行第一列数值: " & xlsSheet.Cells(2,1).Value
msgbox "第二行第二列数值: " & xlsSheet.Cells(2,2).Value
' set cells data:
xlsSheet.Cells(3,1).Value="插入的数据..."
msgbox "第三行第一列数值: " & xlsSheet.Cells(3,1).Value
' define params:
dim ExcelApp
dim objWorkBook
dim objImportSheet
dim path
path = "C:/Users/Administrator/Desktop"
' open excel:
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False
Set objWorkBook = ExcelApp.Workbooks.Open(path & "/1.xls")
Set xlsSheet = objWorkBook.Sheets("Sheet1") '页数名称
' read datas:
msgbox "第一行第一列数值: " & xlsSheet.Cells(1,1).Value
msgbox "第二行第一列数值: " & xlsSheet.Cells(2,1).Value
msgbox "第二行第二列数值: " & xlsSheet.Cells(2,2).Value
' set cells data:
xlsSheet.Cells(3,1).Value="插入的数据..."
msgbox "第三行第一列数值: " & xlsSheet.Cells(3,1).Value
' save
objWorkBook.SaveAs path & "/new.xls"
'objWorkBook.Save
' close
objWorkBook.Close
objWorkBook.SaveAs path & "/new.xls"
'objWorkBook.Save
' close
objWorkBook.Close
' clean & quit:
ExcelApp.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set ExcelApp = Nothing
ExcelApp.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set ExcelApp = Nothing
转载于:https://blog.51cto.com/babyhe/665802