SharePoint and AutoIT

[GreatWest]

I want to use some AutoIT scripts I've written to collect data from various sources. I would then like to have AutoIT place that data into a SharePoint list. I've looked a little at SOAP but I'm not sure if it would be the best way to get the data into the SharePoint list. Any suggestions would be appreceated.

GW

[1ic]

Here's some example code for anyone trying to access Sharepoint with AutoIt.

expandcollapse popup

#include-once
Global $BasicAuthentication = _Base64Encode("username:password")
    ;------------------------------------------------------------------------
    ;Example:
    ; dim $x
    ; $x=_sp_getxml("http://<yoursite>/<your subsite>","<yourlist","")
    ; _SP_DeleteListItems("http://<yoursite>/<your subsite>","<yourlist>",$x)
    ;------------------------------------------------------------------------
Func _SP_GetXML($url,$list, $CAMLQuery)
    ;------------------------------------------------------------------------
    ;Make sure your default view is "AllItems"
    ; $url= URL to your site/subsite
    ; $list= Name of the Sharepointlist to query
    ; $CAMLquery= CAML based query string. Leave empty for the whole list.
    ;Return: XML string from Sharepoint list containing the filtered items
    ;------------------------------------------------------------------------
    Dim $viewFields
    Dim $request
    Dim $xmlDoc
    dim $oStream
    $xmlDoc = objcreate("MSXML2.DOMdocument.6.0")
    $xmlDoc.async = False
    $url = $url & "/_vti_bin/Lists.asmx"
    $viewFields = "<viewFields><FieldRef Name='ows_ID'/></viewFields>"
    $request =  "<?xml version='1.0' encoding='utf-8'?>"
    $request =  $request & "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>"
    $request =  $request & "<soap:Body>"
    $request =  $request & "<GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>"
    $request =  $request & "              <listName>" & $list & "</listName>"
    $request =  $request & "              <ViewName></ViewName>"
    $request =  $request & "              <query>"& $CAMLquery &"</query>"
    $request =  $request & "              <ViewFields></ViewFields>"
    $request =  $request & "              <rowLimit>0</rowLimit>"
    ;$request =  $request & "             <queryOptions></queryOptions>"
    $request =  $request & "              <webID></webID>"
    $request =  $request & "</GetListItems>"
    $request =  $request & "</soap:Body>"
    $request =  $request & "</soap:Envelope>"
    ;post it up and look at the response
    $XmlHttp=ObjCreate("Microsoft.XMLHTTP")
    $XmlHttp.open ("Get", $url, False)
    ;Pass username:password as a base64 string ($BasicAuthentication)
    $XmlHttp.SetRequestHeader ("Authorization", "Basic " & $BasicAuthentication)
    $XmlHttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
    $XmlHttp.SetRequestHeader ("Accept", "text/xml; charset=UTF-8")
    $XmlHttp.setRequestHeader ("SOAPAction","http://schemas.microsoft.com/sharepoint/soap/GetListItems")
    $XmlHttp.send ($request)
    return ($XmlHttp.responsetext)
EndFunc


Func _SP_DeleteListItems($url, $list, $XML)
    ;------------------------------------------------------------------------
    ;Delete all items from a certain list.
    ;------------------------------------------------------------------------
    dim $rs
    dim $str
    dim $objLst
    dim $intT
    dim $batch
    dim $xmlDoc
    $url = $url & "/_vti_bin/Lists.asmx"
    $xmlDoc = objcreate("MSXML2.DOMdocument.6.0")
    $objLst = ObjCreate("MSXML2.DOMdocument.6.0")
    $xmldoc.loadxml($XML)
    $batch="<Batch>"
    $objLst=$xmldoc.getElementsByTagName("z:row")
    for $n in $objLst
        $intT=$intT+1
        $batch = $batch & " <Method ID='" & $intT & "' Cmd='Delete'>"
        $batch = $batch & "<Field Name='ID'>" & $n.getattribute("ows_ID") & "</Field>"
        $batch = $batch & " </Method>"
    next
    $batch= $batch & "</Batch>"
    $request =  "<?xml version='1.0' encoding='utf-8'?>"
    $request =  $request & "<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'"
    $request =  $request & "            xmlns:xsd='http://www.w3.org/2001/XMLSchema'"
    $request =  $request & "            xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>"
    $request =  $request & "  <soap:Body>"
    $request =  $request & "  <UpdateListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>"
    $request =  $request & "              <listName>" & $list & "</listName>"
    $request =  $request & "              <updates>" & $batch & "</updates>"
    $request =  $request & "  </UpdateListItems>"
    $request =  $request & "  </soap:Body>"
    $request =  $request & "</soap:Envelope>"
    $Http=ObjCreate("Microsoft.XMLHTTP")
    $Http.Open ("POST", $Url, false)
    $Http.SetRequestHeader ("Authorization", "Basic " & $BasicAuthentication)
    $Http.setRequestHeader("Content-Type", "text/xml; charset=utf-8")
    $Http.SetRequestHeader ("Accept", "text/xml; charset=UTF-8")
    $Http.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems")
    $Http.Send ($request)
EndFunc

Func _Base64Encode($Data, $LineBreak = 76)
    Local $Opcode = "0x5589E5FF7514535657E8410000004142434445464748494A4B4C4D4E4F505152535455565758595A6162636465666768696A6B6C6D6E6F707172737475767778797A303132333435363738392B2F005A8B5D088B7D108B4D0CE98F0000000FB633C1EE0201D68A06880731C083F901760C0FB6430125F0000000C1E8040FB63383E603C1E60409C601D68A0688470183F90176210FB6430225C0000000C1E8060FB6730183E60FC1E60209C601D68A06884702EB04C647023D83F90276100FB6730283E63F01D68A06884703EB04C647033D8D5B038D7F0483E903836DFC04750C8B45148945FC66B80D0A66AB85C90F8F69FFFFFFC607005F5E5BC9C21000"
    Local $CodeBuffer = DllStructCreate("byte[" & BinaryLen($Opcode) & "]")
    DllStructSetData($CodeBuffer, 1, $Opcode)
    $Data = Binary($Data)
    Local $Input = DllStructCreate("byte[" & BinaryLen($Data) & "]")
    DllStructSetData($Input, 1, $Data)
    $LineBreak = Floor($LineBreak / 4) * 4
    Local $OputputSize = Ceiling(BinaryLen($Data) * 4 / 3)
    $OputputSize = $OputputSize + Ceiling($OputputSize / $LineBreak) * 2 + 4
    Local $Ouput = DllStructCreate("char[" & $OputputSize & "]")
    DllCall("user32.dll", "none", "CallWindowProc", "ptr", DllStructGetPtr($CodeBuffer), _
                                                    "ptr", DllStructGetPtr($Input), _
                                                    "int", BinaryLen($Data), _
                                                    "ptr", DllStructGetPtr($Ouput), _
                                                    "uint", $LineBreak)
    Return DllStructGetData($Ouput, 1)
EndFunc

[exodius]

I want to use some AutoIT scripts I've written to collect data from various sources. I would then like to have AutoIT place that data into a SharePoint list. I've looked a little at SOAP but I'm not sure if it would be the best way to get the data into the SharePoint list. Any suggestions would be appreceated.

GW

Another option is to access the SharePoint list as a linked table in Access (I currently use Access 2007), then you can just do database lookups/reads/modifies to modify what's out on the SharePoint site.

I'm not 100% what level of access you need to have to the list, probably at least Contribute, but under Actions there should be a "Open with Access" option which will open the table for you in Access, then you can just save that as a database file and use some code like this:

expandcollapse popup

#include <Array.au3>
#include <IE.au3>
_IEErrorHandlerRegister()

Global $oArs
Global $oAconn
Const $adOpenStatic = 3
Const $adLockOptimistic = 3
Const $adCmdText = "&H0001"

$vDatabaseLocation = "Location\databasename.accdb"

$oArs.Open('SELECT * FROM TableName WHERE ed_semplid=' & $vEmployeeID & ';', $oAconn, $adOpenStatic, $adLockOptimistic, $adCmdText)
$oArs.MoveFirst
For $iIndex = 1 To $oArs.RecordCount
    
    ;To Read
    MsgBox (0, "", $oArs.Fields("Column Name").value)
    
    ;To Modify 
;~  $oArs2("First Name") = "Jason"

    $oArs.MoveNext
Next

$oArs.Close

Func _OpenDatabaseConnection()
    ConsoleWrite('@@ (' & @ScriptLineNumber & ') :(' & @HOUR & ':' & @MIN & ':' & @SEC & ') _OpenDatabaseConnection()' & @CR) ;### Function Trace
    ; Create a connection object
    $oAconn = ObjCreate("ADODB.Connection")
    $oArs = ObjCreate("ADODB.Recordset")

    ; Open the specified database
;~  $oAconn.Open("Provider= Microsoft.Jet.OLEDB.4.0; Data Source=" & $vDatabaseLocation) ; For Access 2003
    $oAconn.Open("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & $vDatabaseLocation)
    ; Perform the search query to get the outages inside
EndFunc   ;==>_OpenDatabaseConnection

Func _CloseDatabaseConnection()
    ConsoleWrite('@@ (' & @ScriptLineNumber & ') :(' & @HOUR & ':' & @MIN & ':' & @SEC & ') _CloseDatabaseConnection()' & @CR) ;### Function Trace
    ; Closes out the Database objects for re-use
    If IsObj($oArs) Then
        $oArs.Close
    EndIf
    If IsObj($oAconn) Then
        $oAconn.Close
    EndIf
EndFunc   ;==>_CloseDatabaseConnection