VBS 两种方式读取Excel

1：ADODB方式

Function GetConfig(sConfigFile, sSheet,sCaseId)
'	Call PrintLog(Now, LOG_STATUS_INFO, "GetConfig(" & sConfigFile & "," & sSheet & ")")
	Dim oDictionary, oConnection
	Set oDictionary = CreateObject("Scripting.Dictionary")
	Set oConnection = CreateObject("ADODB.Connection")
	oConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sConfigFile & ";Extended Properties=Excel 8.0;Persist Security Info=False;Jet OLEDB"
	
	Dim oRecordSet, sSQL
	Set oRecordSet = CreateObject("ADODB.RecordSet")
	sSQL = "Select * from [" & sSheet & "$] where id = '" & sCaseId & "'"
	oRecordSet.Open sSQL, oConnection, ADO_FWDONLY
	
	Do Until oRecordSet.EOF
		MsgBox oRecordSet("PARENT"))
	oRecordSet.MoveNext
	Loop
	
	Set GetConfig = oDictionary	' Returns
	Set oDictionary = Nothing
	oRecordSet.Close		'Closes database connections
	oConnection.Close
	Set oRecordSet = Nothing
	Set oConnection = Nothing
End Function

2：Application方式

Function getUdfColumnNames(sXlsPath, sSheetName)
	Dim xlsApp, xlsWorkBook, xlsSheet
	Set xlsApp = CreateObject("Excel.Application")
	xlsApp.Visible = False
	Set xlsWorkBook = xlsApp.Workbooks.Open (sXlsPath)
	Set xlsSheet = xlsWorkBook.Sheets(sSheetName)
	
	MsgBox xlsSheet.cells(1,1)
        MsgBox xlsSheet.usedRange.Rows.Count
	
	xlsWorkBook.Close
	xlsApp.Quit
	Set xlsSheet = Nothing
	Set xlsWorkBook = Nothing '释放内存
	Set xlsApp = Nothing  '释放Excel对象
End Function

 

 3，UPDATE

Function updateCellValue(sXlsPath, sSheetName, iRow, iColumn, strValue)
	
	Dim xlsApp, xlsWorkBook, xlsSheet
	
	Set xlsApp = CreateObject("Excel.Application") '创建Excel对象
	
	xlsApp.Visible = False 'true 为显示excel对象，false为不显示
	
	Set xlsWorkBook = xlsApp.Workbooks.Open (sXlsPath) '打开指定路径的Excel表格
	
	Set xlsSheet = xlsWorkBook.Sheets(sSheetName) '选择指定Sheet

	xlsApp.Cells(iRow,iColumn).value = strValue	
	
	xlsWorkBook.Save
	xlsWorkBook.Close
	
	xlsApp.Quit
	
	Set xlsSheet = Nothing
	
	Set xlsWorkBook = Nothing '释放内存
	
	Set xlsApp = Nothing  '释放Excel对象

End Function

 

参考文献： http://www.voiceguide.com/vghelp/source/html/modvbs.htm