Hello All,
I am trying to use VBA to access the metadata from a specific file on SharePoint. Basically there is a live file on SharePoint that is downloaded and used by many users. I want to incorporate a quick code that checks the metadata of the live file to see if the live version number matches the file being used (to then inform the user if their file is outdated).
I am not too familiar with using the SharePoint web services or SOAP - it has been a struggle to find anything relevant on this topic... I managed to do it one way but it required opening the live version in order to access the metadata and this caused too significant of a delay to implement.
I am trying to access the data using the SOAP method (and failing) so wondering if anyone has any suggestions or any better theories on how I can accomplish this? Here's what I've been using so far:
' Set SOAP/Webservice Parameters
Dim SOAPURL_Vers As String, SOAPListName As String, SOAPViewName As String
SOAPURL_Vers = "http://companysitename/link/link/link/link/grouppage/libraryname/_vti_bin/versions.asmx"
SOAPFileName = "sample_mm.xlsb"
Dim SOAPAction As String
SOAPAction = "http://schemas.microsoft.com/sharepoint/soap/GetVersions"
' SOAP Envelope
Dim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String
SOAPEnvelope_Pre = "" & _
"<soap:envelope target="_blank" href="http://www.w3.org/2001/XMLSchema-instance" <a="" xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""XML Schema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
"<soap:body>"
SOAPEnvelope_Pst = "</soap:body>" & _
"</soap:envelope>"
' Complete the packet
Dim SOAPMessage As String
SOAPMessage = SOAPEnvelope_Pre & _
" <getversions xmlns="" target="_blank" href="http://schemas.microsoft.com/sharepoint/soap/" <a="">http://schemas.microsoft.com/sharepoint/soap/"">" & _
" <filename>" & SOAPFileName & "</filename>" & _
" </getversions>" & _
SOAPEnvelope_Pst
From here I was just trying to show the data I was pulling to try and make sense of it all...
' Create HTTP Object
Dim Request As Object
Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")
' Call the service to get the List
Request.Open "POST", SOAPURL_Vers, False
Request.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
Request.setRequestHeader "SOAPAction", SOAPAction
Request.send (SOAPMessage)
MsgBox Request.Status & ":" & Request.statustext & vbCrLf & Request.responseText
MsgBox Right(Request.responseText, 1000)
Debug.Print Request.responseText
It doesn't seem to pull the version number (maybe it does but the data is too lengthy to even show in a message box... Please let me know if you have any better solutions or some way to clean this up and make it work..
Thanks!
I am trying to use VBA to access the metadata from a specific file on SharePoint. Basically there is a live file on SharePoint that is downloaded and used by many users. I want to incorporate a quick code that checks the metadata of the live file to see if the live version number matches the file being used (to then inform the user if their file is outdated).
I am not too familiar with using the SharePoint web services or SOAP - it has been a struggle to find anything relevant on this topic... I managed to do it one way but it required opening the live version in order to access the metadata and this caused too significant of a delay to implement.
I am trying to access the data using the SOAP method (and failing) so wondering if anyone has any suggestions or any better theories on how I can accomplish this? Here's what I've been using so far:
' Set SOAP/Webservice Parameters
Dim SOAPURL_Vers As String, SOAPListName As String, SOAPViewName As String
SOAPURL_Vers = "http://companysitename/link/link/link/link/grouppage/libraryname/_vti_bin/versions.asmx"
SOAPFileName = "sample_mm.xlsb"
Dim SOAPAction As String
SOAPAction = "http://schemas.microsoft.com/sharepoint/soap/GetVersions"
' SOAP Envelope
Dim SOAPEnvelope_Pre As String, SOAPEnvelope_Pst As String
SOAPEnvelope_Pre = "" & _
"<soap:envelope target="_blank" href="http://www.w3.org/2001/XMLSchema-instance" <a="" xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""XML Schema"" xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"">" & _
"<soap:body>"
SOAPEnvelope_Pst = "</soap:body>" & _
"</soap:envelope>"
' Complete the packet
Dim SOAPMessage As String
SOAPMessage = SOAPEnvelope_Pre & _
" <getversions xmlns="" target="_blank" href="http://schemas.microsoft.com/sharepoint/soap/" <a="">http://schemas.microsoft.com/sharepoint/soap/"">" & _
" <filename>" & SOAPFileName & "</filename>" & _
" </getversions>" & _
SOAPEnvelope_Pst
From here I was just trying to show the data I was pulling to try and make sense of it all...
' Create HTTP Object
Dim Request As Object
Set Request = CreateObject("MSXML2.ServerXMLHTTP.6.0")
' Call the service to get the List
Request.Open "POST", SOAPURL_Vers, False
Request.setRequestHeader "Content-Type", "text/xml; charset=utf-8"
Request.setRequestHeader "SOAPAction", SOAPAction
Request.send (SOAPMessage)
MsgBox Request.Status & ":" & Request.statustext & vbCrLf & Request.responseText
MsgBox Right(Request.responseText, 1000)
Debug.Print Request.responseText
It doesn't seem to pull the version number (maybe it does but the data is too lengthy to even show in a message box... Please let me know if you have any better solutions or some way to clean this up and make it work..
Thanks!
