最近一个项目中,需要利用excel访问webservice,读出数据库内容(webservice已与数据库连接好)后,把得到的数据填充到excel中,并在客户端生成一个xml文件记录所读取内容。
现在我做的vba已经可以访问webservice，但是不能填充数据到excel，并且生成的xml文件缺少<?xml version="1.0" standalone="yes"?>。请问该如何解决？？
附有vba代码：
sheet1代码
'Alok Mehta
'MSDN VBA Excel-WebService Example Code
Option Explicit
'WebService Dataset will be temporarily saved in the following XML file
Const XMLFileName = "C:\wd22.xml"
'Professor will be given the following two IDs by the the IT department, these can also be tied to WS tokens
Const Course_ID = 1
Const Professor_ID = 1
Private Sub cmdGetDataFromWebService_Click()
'On Error GoTo Error_Processing
Dim objGrades As New clsws_Service
Dim strReturnValue As String
Dim bSaveFile As Boolean
'Local Counters
Dim intI As Integer
Dim intJ As Integer
Dim intOffset As Integer
intOffset = 5 ' Starting Row
Dim strColumn As String
Dim strValue As String
Dim strFieldName As String
Dim oRoot As MSXML2.IXMLDOMNode
Dim NewXMLdocument As New MSXML2.DOMDocument
strReturnValue = objGrades.wsm_Get_Grades(Professor_ID, Course_ID)
'Save the Returned XML to disk
bSaveFile = SaveToXML(XMLFileName, strReturnValue)
MsgBox strReturnValue
'If the file is saved
If bSaveFile Then
'Load it in Memory
Call NewXMLdocument.Load(XMLFileName)
Set oRoot = NewXMLdocument.documentElement
'Iterate through all the nodes
For intI = 0 To oRoot.childNodes.Length - 1
For intJ = 0 To oRoot.childNodes.Item(intI).childNodes.Length - 1
strValue = oRoot.childNodes.Item(intI).childNodes.Item(intJ).Text
strFieldName = oRoot.childNodes.Item(intI).childNodes.Item(intJ).baseName
'Map the database fieldanme to the Spreadsheet Cells
Select Case strFieldName
Case "ProductID"
strColumn = "A"
Case "Pname"
strColumn = "C"
Case "content"
strColumn = "D"
Case "author"
strColumn = "E"
End Select
'Set the cell's value to XML's node
If IsNull(strValue) = False Then
ActiveSheet.Cells(intI + intOffset, strColumn).Value = strValue
End If
Debug.Print strFieldName, strValue
Next
Next
'Show that we are done
MsgBox "Web Service Data Get was successful"
End If
Set objGrades = Nothing
Exit Sub
Error_Processing:
MsgBox "Error in Web Service Data Get " & Err.Description
End Sub
Private Function SaveToXML(strValue As String, strFileName As String) As Boolean
On Error GoTo Error_Processing
Dim fs As Object
Dim a
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile(strValue, False)
a.WriteLine (strFileName)
a.Close
SaveToXML = True
Exit Function
Error_Processing:
MsgBox "Cannot Save XML File " & Err.Description
SaveToXML = False
End Function
类模块代码：
'Dimensioning private class variables.
Private sc_Service As SoapClient30
Private Const c_WSDL_URL As String = "http://localhost:1784/testexcel/Service.asmx?wsdl"
Private Const c_SERVICE As String = "Service"
Private Const c_PORT As String = "ServiceSoap"
Private Const c_SERVICE_NAMESPACE As String = "http://myCollege.edu/"
Private Sub Class_Initialize()
'*****************************************************************
'This subroutine will be called each time the class is instantiated.
'Creates sc_ComplexTypes as new SoapClient30, and then
'initializes sc_ComplexTypes.mssoapinit2 with WSDL file found in
'http://localhost/grades/WebService.asmx?wsdl.
'*****************************************************************
Dim str_WSML As String
str_WSML = ""
Set sc_Service = New SoapClient30
sc_Service.MSSoapInit2 c_WSDL_URL, str_WSML, c_SERVICE, c_PORT, c_SERVICE_NAMESPACE
'Use the proxy server defined in Internet Explorer's LAN settings by
'setting ProxyServer to <CURRENT_USER>
sc_Service.ConnectorProperty("ProxyServer") = "<CURRENT_USER>"
'Autodetect proxy settings if Internet Explorer is set to autodetect
'by setting EnableAutoProxy to True
sc_Service.ConnectorProperty("EnableAutoProxy") = True
End Sub
Private Sub Class_Terminate()
'*****************************************************************
'This subroutine will be called each time the class is destructed.
'Sets sc_ComplexTypes to Nothing.
'*****************************************************************
'Error Trap
On Error GoTo Class_TerminateTrap
Set sc_Service = Nothing
Exit Sub
Class_TerminateTrap:
ServiceErrorHandler ("Class_Terminate")
End Sub
Private Sub ServiceErrorHandler(str_Function As String)
'*****************************************************************
'This subroutine is the class error handler. It can be called from any class subroutine or function
'when that subroutine or function encounters an error. Then, it will raise the error along with the
'name of the calling subroutine or function.
'*****************************************************************
'SOAP Error
If sc_Service.FaultCode <> "" Then
Err.Raise vbObjectError, str_Function, sc_Service.FaultString
'Non SOAP Error
Else
Err.Raise Err.Number, str_Function, Err.Description
End If
End Sub
Public Function wsm_Get_Grades(ByVal dcml_nProfessorID As Double, ByVal dcml_lngCourseID As Double) As String
'*****************************************************************
'Proxy function created from http://localhost/grades/WebService.asmx?wsdl.
'
'"wsm_Get_Grades" is defined as XML. See Complex Types: XML Variables in
'Microsoft Office 2003 Web Services Toolkit Help for details on implementing XML variables.
'*****************************************************************
'Error Trap
On Error GoTo wsm_Get_GradesTrap
wsm_Get_Grades = sc_Service.Get_Grades(dcml_nProfessorID, dcml_lngCourseID)
Exit Function
wsm_Get_GradesTrap:
ServiceErrorHandler "wsm_Get_Grades"
End Function
麻烦大家帮忙下，很着急。谢谢各位
[返回] 中文XML论坛 - 专业的XML技术讨论区 →
XML.ORG.CN讨论区 - XML技术 → 『 XML源码及示例(仅原创和转载) 』 → 一个烦恼的问题
(订阅本版)
2007/7/31 1:34:00
新浪微博
一个烦恼的问题
