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.
AccountantsTechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jamie McAllister MVPSharePoint ConsultantCommented:
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
0
AccountantsTechAuthor Commented:
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.
0
Jamie McAllister MVPSharePoint ConsultantCommented:
If you choose the web services route this should help;

https://github.com/VBA-tools/VBA-Web

Also came across this sample code;

https://onedrive.live.com/?cid=c0f61214d82ac938&id=C0F61214D82AC938%21271&authkey=!AFUOiFwECebXcpY

(from this thread; http://sharepoint.stackexchange.com/questions/68531/query-a-sharepoint-2010-list-from-excel-vba-to-retrieve-a-specific-record)

BTW it was possible to do calls direct from Excel VBA, I've seen samples in the past though this may be being phased out;

http://sharepoint.stackexchange.com/questions/11426/update-list-from-excel-using-vba
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jamie McAllister MVPSharePoint ConsultantCommented:
0
AccountantsTechAuthor Commented:
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?
0
Jamie McAllister MVPSharePoint ConsultantCommented:
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.
0
AccountantsTechAuthor Commented:
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:)
0
Jamie McAllister MVPSharePoint ConsultantCommented:
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?
0
AccountantsTechAuthor Commented:
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
0
AccountantsTechAuthor Commented:
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.
0
Jamie McAllister MVPSharePoint ConsultantCommented:
Well you're the one who specified Excel!! ;)
0
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SharePoint

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.