|
本帖最后由 lanchozxd 于 2011-6-27 14:20 编辑
回复 1# 开朗幽默 给个思路吧,下边代码基本可以实现操作,但是文件关闭,变量=nothing等没写出来
’ 打开workbook
set workBook = openExcel(filepath) ’ 取得某个sheet
set xlsSheet = workBook.Worksheets("业务类型管理") dim cellValue
dim cellValue ’ 定义
cellValue = getCellValue(xlsSheet ,2,1) ' 取值
' 关闭excel
call closeExcel(workBook)
' 打开excel
Function openExcel(filePath)
err = 0
On error resume next
'定义excel file
Dim objExcel
set objExcel = CreateObject("Excel.Application")
' 定义workbook
Dim workBook
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists (filePath) Then
Set workBook = objExcel.Workbooks.Open(filePath)
else
msgbox "指定的文件不存在,请确认",vbokonly , "打开失败"
Exit function
End If
set openExcel = workBook
If err.number <>0 Then
msgbox "打开excel文件失败:" _
& err.description,vbokonly , "打开失败"
End If
On error goto 0 ' 关闭,置空
fso.close
set fso =nothing
set objExcel =nothing
set workbook =nothing
End Function
'=======================================
' 取得指定sheet指定cell的值
' @ parameter xlsSheet sheet对象
' @ parameter rowNum 行数
' @ parameter colNum l列数
' @ return getCellValue
'=======================================
Function getCellValue(xlsSheet, rowNum, colNum)
' 存放cell的值
Dim actualValue
actualValue =trim(cstr( xlsSheet.cells(rowNum, colNum)))
err = 0
On error resume next
If err.number <> 0 Then
getCellValue = "#can not get the right value#"
else
' 返回值
getCellValue = actualValue
End If
End Function
' ===================================
' 关闭excel 文件
' close a opende excel file
' @parameter workbook
' ===================================
Function closeExcel(workbook)
err = 0
on error resume next
' 关闭excel前 先保存
workbook.save
workbook.close
If err.number <>0 Then
msgbox "关闭excel失败,请确认!",vbokonly,"提示信息"
End If
On error goto 0
End Funct |
|