HuyPV
Wednesday, March 24, 2010
Function updateSMSCode(id, smsCode)
Dim worksheetName
worksheetName = "[Worksheet$]" 'Sheet1
Dim xlsFile
xlsFile = "C:\Documents and Settings\LNV\Desktop\ex-code\test.xls"
Const adUseClient = 3
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & xlsFile & ";Extended Properties=Excel 8.0;"
'.Provider = "MSDASQL"
'.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Documents and Settings\LNV\Desktop\ex-code\test.xls;"
.CursorLocation = adUseClient
.Open
End With
' Set rsT = CreateObject("ADODB.RecordSet")
' rst.Open "SELECT * FROM [Sheet1$]", cn, 1
' While Not rst.EOF
' For Each x In rst.Fields
' WScript.Echo x.Name & " = " & x.value
' Next
' rst.MoveNext
' Wend
' rst.Close
Dim rAffected
rAffected = 0
cn.Execute "UPDATE " & worksheetName & " SET code=" & smsCode & " WHERE id=" & id, rAffected
If rAffected > 0 Then
blnRet = True
Else
blnRet = False
End If
'cn.Execute "UPDATE [Sheet1$] SET uploaded=TRUE, name='LOZ " & Date() & "' WHERE id=1"
'cn.Execute "INSERT INTO [Sheet1$] (id, name, uploaded, sms_code) VALUES (3, 'Ka ka', TRUE, '34567')"
cn.Close
'Set rst = Nothing
Set cn = Nothing
updateSMSCode = blnRet
End Function
If WScript.Arguments.Count() <> 2 Then
WScript.Echo "Usage: cscript " & WScript.ScriptName & " id smsCode"
WScript.Quit
Else
id = WScript.Arguments.Item(0)
smsCode = WScript.Arguments.Item(1)
WScript.Echo id & " => " & smsCode
If updateSMSCode(id, smsCode) Then
WScript.Echo "successed"
Else
WScript.Echo "failed"
End if
End If
Title:
Use VBS & ADODB to insert, update, delete data in excel file
Description:
Function updateSMSCode(id, smsCode) Dim worksheetName worksheetName = "[Worksheet$]" 'Sheet1 Dim xlsFile x...
...
Rating:
4