假定你想用Excel连接到DB, 而又不想把Oracle Client安装到用户端,那你可以采用下面的方法:用Web Services技术去连接DB;
你有一个Web Services的URL: http://129.15.67.89/DBConnect.WebService/DBConnect.WebService.asmx?wsdl
这个Web Services 的功能是连接DB 和处理Sql 语句的功能;
其二,要在用户端,安装这个类 MSSOAPLib30.SoapClient30;做为DB Connect WebSerivces 引用;
那么你在你的excel宏编程可以采用如下代码:
'Sample of VBA Call WebServce
Dim strSql As String
Dim strTemp As String
Dim strMsg As String
Dim strSchema As String
Dim wsDBConn As MSSOAPLib30.SoapClient30
----DB connect webservice
Dim strDBConnUrl As String
Dim blnExecSql As Boolean
Dim strUpdateNewUSNWhenOldExist As String
strDataSheetName = "Test DB"
strConfigSheetName = "Test Program"
On Error GoTo Err1
strDBConnUrl = http://129.15.67.89/DBConnect.WebService/DBConnect.WebService.asmx?wsdl
Set wsDBConn = New MSSOAPLib30.SoapClient30 ’引用Soapclient
wsDBConn.MSSoapInit strDBConnUrl ' 连接Oracle DB
strSql = "SELECT COUNT(*) FROM USNTEMP WHERE LDUSN='" & strOldUSN & "'"
strTemp = wsDBConn.ExecuteScalarInSchema(strSql)
这样就 OK啦...
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30193/viewspace-547153/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30193/viewspace-547153/