asked on

ADO Connection to SharePoint

I am trying to connect to our company SharePoint site to retrieve and edit the records. I would like to be able to pull the information into excel and then make changes on it and send it back to SharePoint.

I need to make a connection to the SharePoint and query the data so that I can pull specific records. Then I need to be able to send those updates back to the site.

I have found some information on connecting to the site but I do not know the table names to be able to query the data and make changes.

Any help would be greatly appreciated.

Jamie McAllister

I'm afraid you're adopting the wrong approach. You cannot and should not query the database directly. This can induce locking and would leave you in an unsupported state.

If you're working with SharePoint 2007 consider working with the web services to read and edit list data.

If 2010 or later consider web services or client object model, depending on what data you're after.

Let me know more about your version and use case.

Here are some helpful links;

https://msdn.microsoft.com/en-us/library/office/fp179912.aspx

http://geekswithblogs.net/KunaalKapoor/archive/2012/11/26/csom-client-side-object-model---whats-new-with-sharepoint.aspx

http://www.infoq.com/articles/swanson-moss-web-services

AccountantsTech

ASKER

Thank you for your quick response.

We are currently using SharePoint 2010. I would like to be able to control the data with excel vba. That way we could look up the documents for a particular client and then read and make changes to the files.

The ideal way would be if there is an object library in vba to connect to SharePoint and edit the records.

ASKER CERTIFIED SOLUTION

Jamie McAllister

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jamie McAllister

Here's another good example;

http://stackoverflow.com/questions/20116896/import-sharepoint-2010-list-data-from-excel-table-using-vba

AccountantsTech

ASKER

I have now got the XML response the problem is I am not familiar with XML so i am at a lose of how to get or update any useful information.

I have the MSXML2.XMLHTTP object pulling info but i just need to be able to read this and then update the specific record.

Are you able to direct this novice in the right direction?

Jamie McAllister

Last time I did any major VBA dev it was still the twentieth century. I am however familiar with XML and SharePoint.

There's another sample here showing where the new values go in the XML, and how to submit that to update an item;

http://stackoverflow.com/questions/22450717/add-and-update-single-item-in-sharepoint-list-via-vba

Feel free to post your code and a section of the XML for further assistance.

AccountantsTech

ASKER

This is the code that I working with.

Public Sub SharePointXml()
Dim objXMLHTTP As MSXML2.XMLHTTP

Dim strListNameOrGuid As String
Dim strBatchXml As String
Dim strSoapBody As String

Set objXMLHTTP = New MSXML2.XMLHTTP

strListNameOrGuid = "guid address"

' Delete item with internal ID of "1"
strBatchXml = "<Batch OnError='Continue'><Method ID='1' Cmd='Delete'><Field Name='ID'>1</Field></Method></Batch>"

objXMLHTTP.Open "POST", "site address", False
objXMLHTTP.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
objXMLHTTP.setRequestHeader "SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/UpdateListItems"

strSoapBody = "<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/'><soap:Body><UpdateListItems " _
  & "xmlns='http://schemas.microsoft.com/sharepoint/soap/'><listName>" & strListNameOrGuid _
  & "</listName><updates>" & strBatchXml & "</updates></UpdateListItems></soap:Body></soap:Envelope>"

objXMLHTTP.Send strSoapBody

If objXMLHTTP.Status = 200 Then
    Debug.Print objXMLHTTP.responseText
    ' Do something with response
End If

Set objXMLHTTP = Nothing

End Sub

Open in new window

the output of the Debug.Print is

function WPQ2AddColumn() 
{ 
  CoreInvoke('GCAddNewColumn', document.STSListControlWPQ2 , "http://tserver01" );
} 

function WPQ2ChangeColumn() 
{ 
  CoreInvoke('GCEditDeleteColumn', document.STSListControlWPQ2 , "http://tserver01" );
} 
function WPQ2GridNewFolder() 
{ 
  CoreInvoke('GCNewFolder', document.STSListControlWPQ2 );
} 

</script>
<script type="text/javascript" for="STSListControlWPQ2" event="onresize">
CoreInvoke('GCOnResizeGridControl',document.STSListControlWPQ2);
</script>
<script type="text/javascript" for="window" event="onresize">
CoreInvoke('GCWindowResize',document.STSListControlWPQ2);
</script>
<script type="text/javascript">
 function _spBodyOnLoad()
 {
     CoreInvoke('GCWindowResize',document.STSListControlWPQ2);
     CoreInvoke('GCActivateAndFocus',document.STSListControlWPQ2);
 }
</script>
<script type="text/javascript" for="document" event="onreadystatechange">
 if (document.readyState == "complete") 
 {
     if (CoreInvoke('TestGCObject', document.STSListControlWPQ2)) 
     { 
         CoreInvoke('GCActivateAndFocus',document.STSListControlWPQ2);
     } 
     else 
      { 
         CoreInvoke('GCNavigateToNonGridPage'); 
     } 
 }
</script>
<script type="text/javascript">
var bGridViewPresent = true;
</script>
</div></td>
            </tr>
        </table></td>
    </tr>
</table>

                            </div>
                        </div></div>
                    <div class="s4-die">
                        
                        
                    </div>
                </div>
            </div>
            
        </div>
</div>
  
      
  
   <input type="text" name="__spText1" title="text" style="display:none;" />
   <input type="text" name="__spText2" title="text" style="display:none;" />
  <div id="ctl00_panelZone">
    <div style='display:none' id='hidZone'><menu id="MSOMenu_WebPartMenu" class="ms-SrvMenuUI">
        <ie:menuitem title="Collapse this web part." id="MSOMenu_Minimize" onmenuclick="javascript:MSOLayout_MinimizeRestore(MenuWebPart)" text="Minimize" type="option">

        </ie:menuitem><ie:menuitem title="Expand this web part." id="MSOMenu_Restore" onmenuclick="javascript:MSOLayout_MinimizeRestore(MenuWebPart)" text="Restore" type="option">

        </ie:menuitem><ie:menuitem title="Close this Web Part. You can still find it under closed Web Parts section in the insert ribbon. These changes will apply to all users." id="MSOMenu_Close" onmenuclick="javascript:MSOLayout_RemoveWebPart(MenuWebPart)" text="Close" type="option">

        </ie:menuitem><ie:menuitem title="Delete this Web Part from the page. These changes will apply to all users." id="MSOMenu_Delete" iconsrc="/_layouts/images/DelItem.gif" onmenuclick="if(confirm('You are about to permanently delete this Web Part. Are you sure you want to do this?')) {MSOWebPartPage_partDeleted = MenuWebPartID;MSOWebPartPage_MenuDoPostBack('ctl00$m', MenuWebPartID + ';MSOMenu_Delete');}" text="Delete" type="option">

        </ie:menuitem><ie:menuitem type="separator"></ie:menuitem><ie:menuitem title="Change properties of my Web Part. These changes will apply only to me." id="MSOMenu_Edit" iconsrc="/_layouts/images/EditItem.gif" onmenuclick="javascript:MSOTlPn_ShowToolPane2Wrapper('Edit', 16, MenuWebPartID)" text="Edit My Web Part" type="option">

        </ie:menuitem><ie:menuitem title="Show connections options for this Web Part. These changes will apply to all users." id="MSOMenu_Connections" onmenuclick="" text="Connections" type="option">

        </ie:menuitem><ie:menuitem type="separator"></ie:menuitem><ie:menuitem title="Export this Web Part. These changes will apply to all users." id="MSOMenu_Export" onmenuclick="javascript:MSOWebPartPage_ExportCheckWarning(&#39;\u002f_vti_bin\u002fexportwp.aspx?pageurl=http\u00253A\u00252F\u00252Ftserver01\u00253A80\u00252FGehman\u00252520Workflow\u00252FForms\u00252FA\u00252520Company\u00252520View\u00252Easpx\u0026guidstring=&#39;+ escape(MenuWebPartID), MenuWebPart.getAttribute(&#39;HasPers&#39;) == &#39;true&#39;)" text="Export..." type="option">

        </ie:menuitem><ie:menuitem title="Restore the defaults of this Web Part. These changes will apply to all users." id="MSOMenu_RestorePartDefaults" onmenuclick="MSOWebPartPage_RestorePartDefaults(MenuWebPartID);" text="Reset Web Part Content" type="option">

        </ie:menuitem><ie:menuitem id="MSOMenu_Help" iconsrc="/_layouts/images/HelpIcon.gif" onmenuclick="MSOWebPartPage_SetNewWindowLocation(MenuWebPart.getAttribute('helpLink'), MenuWebPart.getAttribute('helpMode'))" text="Help" type="option" style="display:none">

        </ie:menuitem>
    </menu></div>
</div><input type='hidden' id='_wpcmWpid' name='_wpcmWpid' value='' /><input type='hidden' id='wpcmVal' name='wpcmVal' value=''/>

<script type="text/javascript">
//<![CDATA[
var _spFormDigestRefreshInterval = 1440000;function loadMDN2() { EnsureScript('MDN.js', typeof(loadFilterFn), null); }
function loadMDN1() { ExecuteOrDelayUntilScriptLoaded(loadMDN2, 'sp.ribbon.js'); }
_spBodyOnLoadFunctionNames.push('loadMDN1');
function _spNavigateHierarchyEx(nodeDiv, dataSourceId, dataPath, url, listInContext, type, additionalQString) {
    SetAdditionalNavigateHierarchyQString(additionalQString);
    g_originalSPNavigateFunc(nodeDiv, dataSourceId, dataPath, url, listInContext, type);
}

g_originalSPNavigateFunc = _spNavigateHierarchy;
_spNavigateHierarchy = _spNavigateHierarchyEx;
var _fV4UI = true;
function _RegisterWebPartPageCUI()
{
    var initInfo = {editable: false,isEditMode: false,allowWebPartAdder: false};
    SP.Ribbon.WebPartComponent.registerWithPageManager(initInfo);
    var wpcomp = SP.Ribbon.WebPartComponent.get_instance();
    var hid;
    hid = document.getElementById("_wpSelected");
    if (hid != null)
    {
        var wpid = hid.value;
        if (wpid.length > 0)
        {
            var zc = document.getElementById(wpid);
            if (zc != null)
                wpcomp.selectWebPart(zc, false);
        }
    }
    hid = document.getElementById("_wzSelected");
    if (hid != null)
    {
        var wzid = hid.value;
        if (wzid.length > 0)
        {
            wpcomp.selectWebPartZone(null, wzid);
        }
    }
}
ExecuteOrDelayUntilScriptLoaded(_RegisterWebPartPageCUI, "sp.ribbon.js"); var __wpmExportWarning='This Web Part Page has been personalized. As a result, one or more Web Part properties may contain confidential information. Make sure the properties contain information that is safe for others to read. After exporting this Web Part, view properties in the Web Part description file (.WebPart) by using a text editor such as Microsoft Notepad.';var __wpmCloseProviderWarning='You are about to close this Web Part.  It is currently providing data to other Web Parts, and these connections will be deleted if this Web Part is closed.  To close this Web Part, click OK.  To keep this Web Part, click Cancel.';var __wpmDeleteWarning='You are about to permanently delete this Web Part.  Are you sure you want to do this?  To delete this Web Part, click OK.  To keep this Web Part, click Cancel.';var objStsSync = GetStssyncData('documents','Client', '', '/_layouts/images/menu');
var offlineBtnText = '';var offlineBtnImg = '';if(objStsSync){ offlineBtnText = objStsSync.BtnText;
offlineBtnImg = objStsSync.BtnImagePath;
}
WebForm_InitCallback();var _spWebPermMasks = {High:432,Low:1011028719};//]]>
</script>
<script type="text/javascript" language="JavaScript" defer="defer">
<!--
function SearchEnsureSOD() { EnsureScript('search.js',typeof(GoSearch)); } _spBodyOnLoadFunctionNames.push('SearchEnsureSOD');function S3031AEBB_Submit() {if (document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_ctl05').value == '0') { document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_S3031AEBB_InputKeywords').value=''; }SearchEnsureSOD();GoSearch('ctl00_PlaceHolderSearchArea_ctl01_ctl05','ctl00_PlaceHolderSearchArea_ctl01_S3031AEBB_InputKeywords',null,true,false,null,'ctl00_PlaceHolderSearchArea_ctl01_ctl00','ctl00_PlaceHolderSearchArea_ctl01_ctl01',null,'This List','\u002f_layouts\u002fOssSearchResults.aspx', 'This Site','This List', 'This Folder', 'Related Sites', '\u002f_layouts\u002fOSSSearchResults.aspx', '', 'Please enter one or more search words.');if (document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_ctl05').value == '0') { document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_S3031AEBB_InputKeywords').value=''; }}
// -->
</script><script type="text/javascript" language="JavaScript" >
// append an onload event handler
$addHandler(window, 'load', function() {
  document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_S3031AEBB_InputKeywords').name = 'InputKeywords';
});
function S3031AEBB_OSBEK(event1) { 
var kCode = String.fromCharCode(event1.keyCode);
if(kCode == "\n" || kCode == "\r")
{   
S3031AEBB_Submit();return false;
}
}
{ var searchTextBox = document.getElementById('ctl00_PlaceHolderSearchArea_ctl01_S3031AEBB_InputKeywords');if (searchTextBox.className.indexOf('s4-searchbox-QueryPrompt') == -1) searchTextBox.className += searchTextBox.className?' s4-searchbox-QueryPrompt':'s4-searchbox-QueryPrompt'; }// -->
</script><script type="text/javascript" >
<!--
WPSC.Init(document);
var varPartWPQ2 = WPSC.WebPartPage.Parts.Register('WPQ2','8beb839b-f081-4342-8c6a-9c4822725009',document.getElementById('WebPartWPQ2'));
WPSC.WebPartPage.WebURL = 'http:\u002f\u002ftserver01';
WPSC.WebPartPage.WebServerRelativeURL = '\u002f';

//-->
</script><script type="text/javascript">
// <![CDATA[
// ]]>
</script>
<script type="text/javascript">RegisterSod("sp.core.js", "\u002f_layouts\u002fsp.core.js?rev=7ByNlH\u00252BvcgRJg\u00252BRCctdC0w\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("sp.res.resx", "\u002f_layouts\u002fScriptResx.ashx?culture=en\u00252Dus\u0026name=SP\u00252ERes\u0026rev=b6\u00252FcRx1a6orhAQ\u00252FcF\u00252B0ytQ\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("sp.ui.dialog.js", "\u002f_layouts\u002fsp.ui.dialog.js?rev=Tpcmo1\u00252FSu6R0yewHowDl5g\u00253D\u00253D");RegisterSodDep("sp.ui.dialog.js", "sp.core.js");RegisterSodDep("sp.ui.dialog.js", "sp.res.resx");</script>
<script type="text/javascript">RegisterSod("core.js", "\u002f_layouts\u002f1033\u002fcore.js?rev=2cAv29JI6EzPZPYbnTTlQg\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("sp.runtime.js", "\u002f_layouts\u002fsp.runtime.js?rev=IGffcZfunndj0247nOxKVg\u00253D\u00253D");RegisterSodDep("sp.runtime.js", "sp.core.js");RegisterSodDep("sp.runtime.js", "sp.res.resx");</script>
<script type="text/javascript">RegisterSod("sp.js", "\u002f_layouts\u002fsp.js?rev=\u00252B4ZEyA892P3T0504qi0paw\u00253D\u00253D");RegisterSodDep("sp.js", "sp.core.js");RegisterSodDep("sp.js", "sp.runtime.js");RegisterSodDep("sp.js", "sp.ui.dialog.js");RegisterSodDep("sp.js", "sp.res.resx");</script>
<script type="text/javascript">RegisterSod("cui.js", "\u002f_layouts\u002fcui.js?rev=OOyJv78CADNBeet\u00252FvTvniQ\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("inplview", "\u002f_layouts\u002finplview.js?rev=WB6Gy8a027aeNCq7koVCUg\u00253D\u00253D");RegisterSodDep("inplview", "core.js");RegisterSodDep("inplview", "sp.js");</script>
<script type="text/javascript">RegisterSod("ribbon", "\u002f_layouts\u002fsp.ribbon.js?rev=F\u00252BUEJ66rbXzSvpf7nN69wQ\u00253D\u00253D");RegisterSodDep("ribbon", "core.js");RegisterSodDep("ribbon", "sp.core.js");RegisterSodDep("ribbon", "sp.js");RegisterSodDep("ribbon", "cui.js");RegisterSodDep("ribbon", "sp.res.resx");RegisterSodDep("ribbon", "sp.runtime.js");RegisterSodDep("ribbon", "inplview");</script>
<script type="text/javascript">RegisterSod("sp.ui.policy.resources.resx", "\u002f_layouts\u002fScriptResx.ashx?culture=en\u00252Dus\u0026name=SP\u00252EUI\u00252EPolicy\u00252EResources\u0026rev=YhBHGmUAGyJ3lAgSdE4V\u00252Fw\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("mdn.js", "\u002f_layouts\u002fmdn.js?rev=gwmFFJ2\u00252FfFacqXWAqG\u00252FqKg\u00253D\u00253D");RegisterSodDep("mdn.js", "sp.core.js");RegisterSodDep("mdn.js", "sp.runtime.js");RegisterSodDep("mdn.js", "sp.js");RegisterSodDep("mdn.js", "cui.js");RegisterSodDep("mdn.js", "ribbon");RegisterSodDep("mdn.js", "sp.ui.policy.resources.resx");</script>
<script type="text/javascript">RegisterSod("WPAdderClass", "\u002f_layouts\u002fwpadder.js?rev=rmznE9UTHIeAZF\u00252FGRiGNVA\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSod("search.js", "\u002f_layouts\u002fsearch.js?rev=BjP0\u00252BmPXUFhF7kDZmHIaVg\u00253D\u00253D");</script>
<script type="text/javascript">RegisterSodDep("browserScript", "msstring.js");</script>
<script type="text/javascript">
//<![CDATA[

theForm.oldSubmit = theForm.submit;
theForm.submit = WebForm_SaveScrollPositionSubmit;

theForm.oldOnSubmit = theForm.onsubmit;
theForm.onsubmit = WebForm_SaveScrollPositionOnSubmit;
Sys.Application.initialize();
function init_zz14_TopNavigationMenuV4() {$create(SP.UI.AspMenu, null, null, null, $get('zz14_TopNavigationMenuV4'));}ExecuteOrDelayUntilScriptLoaded(init_zz14_TopNavigationMenuV4, 'sp.js');
function init_zz15_V4QuickLaunchMenu() {$create(SP.UI.AspMenu, null, null, null, $get('zz15_V4QuickLaunchMenu'));}ExecuteOrDelayUntilScriptLoaded(init_zz15_V4QuickLaunchMenu, 'sp.js');
//]]>
</script>
</form>
  
    
</body>
</html>

Open in new window

I hope that makes more sense to you then it does to me:)

Jamie McAllister

Why are you calling the Delete method in your first bit of code?

The debug print looks like some page has been returned. It's not the valid XML representing some list items. What sort of URL did you provide the code to query the list?

AccountantsTech

ASKER

This code is basically just code from one of the sources that you gave me with our site info inserted. I don't know the XML methods so that strBatchXml like that calls a Delete method is just the sample code that the site had posted.

I would be looking for running a Select and Update method for my data.

objXMLHTTP.Open "POST", "http://tserver01/Gehman%20Workflow/Forms/A%20Company%20View.aspx", False
Is the complete line for the URL

AccountantsTech

ASKER

Thank you for your help on this. I actually found an easier route. You can link the SharePoint List to an Access table and then work with the data from there.

Jamie McAllister

Well you're the one who specified Excel!! ;)

Martin Liss

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.