在Excel VBA 宏编程里,用Web Services技术去连接DB

       假定你想用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/