VBScript读取 Excel 与 写入 Excel


Function  GetCellValue(excelSheet, row, column, path)
on error resume next
Set  Wshshell  = CreateObject ( " Wscript.shell " )
Set  ExcelApp  = CreateObject ( " excel.Application " )
        ExcelApp.Visible  = True
Set  newBook  =  ExcelApp.Workbooks.Open(path)
If   Err  = 0 Then
Set  excelSheet  =  ExcelApp.ActiveSheet
                GetCellValue  =  excelSheet.Cells(row, column)
                ExcelApp.Quit
                Wshshell.Popup GetCellValue, 2 , " 获取的Excel单元格的值为: " , 0 + 64
Else
                Wshshell.Popup  " 请确认文件是否已经创建 " , 3 , " 文件不存在 " , 0 + 64
                ExcelApp.Quit
End If
End Function
Call  GetCellValue( " excel " , 9 , 9 , " E:/excel.xls " )

写入:
Function  WriteExcel(row,col,value,path)
Set  Wshshell  = Createobject ( " Wscript.shell " )
        Err  = 0
on error resume next
Dim  fso,f
Set  fso  = CreateObject ( " Scripting.FileSystemObject " )
Set  f  =  fso.GetFile(path)
Set  ExcelApp  = CreateObject ( " Excel.Application " )
        ExcelApp.Visible  = true
If   Err  = 0 Then
Set  newBook  =  ExcelApp.Workbooks.Open(path)
                newBook.Worksheets( 1 ).Activate
                newBook.Worksheets( 1 ).Cells(row,col).value = value
                newBook.Worksheets( 1 ).Name = " excel "
                newBook.Save
                ExcelApp.Application.quit
set  newBook  = nothing
Set  ExcelApp  = nothing
Elseif  Err  = 53 Then
Set  newBook  =  ExcelApp.Workbooks.Add
                newBook.Worksheets( 1 ).Activate
                newBook.Worksheets( 1 ).Cells(row,col).value = value                
                newBook.Worksheets( 1 ).Name = " excel "
                newBook.SaveAs path
                ExcelApp.Application.quit
set  newBook  = nothing
Set  ExcelApp  = nothing
Else
                Wshshell.Popup  " 发生未知错误 " ,  5 ,  " 无法继续 " , 0 + 32
End If
End Function
Call  WriteExcel( 9 , 9 , " Xiesh" , " E:/excel.xls " )

以上代码中, WriteExcel(row,col,value,path)的四个参数要求传递常量,否则无法写入excel.

Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s