How To Include The Document Version With Export To Excel From Sharepoint

2 minute read

When you have a SharePoint Document Library and use the Export to Excel feature, you may have noticed that document versions will not be exported to the workbook. This is the VBA-code created which reads the version-info from your SharePoint-document library and inserts the version-info into your Excel file.

The code includes 2 main sub routines, and a third which is called:

  • Sub Routine GetVersionInfoFromSP() reads the version info from SharePoint, this calls the GetCommandText routine.
  • Sub Routine PopulateVersionInfo() inserts the version-info into the Excel-file

Before we run the code…

Check that there is a reference to the MSXML.DLL. To do this, go in the VBA-editor, point to TOOLS -> REFERENCES and add a reference to Microsoft XML, v.4.0. If the reference is missing you’ll get a compile error on this line:

Then the following registry keys may not exist on your machine which are required to fulfill the request.

  • [HKEY_CLASSES_ROOTMsxml2.DOMDocument.4.0]
  • [HKEY_CLASSES_ROOTMsxml2.DOMDocument.4.0CLSID]
  • [HKEY_CLASSES_ROOTMsxml2.DOMDocument.4.0CLSID(Default) = {88D969C0-F192-11D4-A65F-0040963251E5}]

You’ll need to download and install the MSXML 4.0 pack from the following source: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7f6c0cb4-7a5e-4790-a7cf-9e139e6819c0

Let’s do this

Frist routine to run is GetVersionInfoFromSP(). The version-info from the SharePoint Document Library will be read into an array.

Then, run the PopulateVersionInfo(). This code will insert the version-info into your Excel file.

'========================================================================================
'## Get the Version number from Sharepoint with Export to Excel
'======================================================================================== Dim sViewGUID As String
Dim sListGUID As String
Dim sListWeb As String
Dim sarValues() As String '## Firstly, run the GetVersionInfoFromSP sub routine, which calls
' the GetCommandText routine
Sub GetVersionInfoFromSP() Dim objDoc Dim objHTTP Dim sGet As String Dim viewNodes Dim i As Integer ' get the data connection info GetCommandText ' we add dummy param dt and set it to the current date/time so ' the result will not be cached. sGet = sListWeb & "/owssvr.dll?Cmd=Display&List=" _ & sListGUID & "&View=" & sViewGUID & "&XMLDATA=TRUE&dt=" & Now Set objDoc = CreateObject("Msxml2.DOMDocument") Set objHTTP = CreateObject("Msxml2.XMLHTTP") objHTTP.Open "GET", sGet, False ' make the call and get the response from the server objHTTP.send Set objDoc = objHTTP.responseXML objDoc.SetProperty "SelectionLanguage", "XPath" Set viewNodes = objDoc.DocumentElement.SelectNodes("//*/*/@ows__UIVersionString") ' get the version information ReDim sarValues(1 To viewNodes.Length, 1 To 1) For i = 1 To viewNodes.Length sarValues(i, 1) = viewNodes.Item(i - 1).Value Next Set objHTTP = Nothing Set objDoc = Nothing
End Sub Sub GetCommandText() Dim sCmdText As String Dim objDoc As New MSXML2.DOMDocument40 ' get the view guid, list guid and url from the Connection object sCmdText = ActiveWorkbook.Connections(1).OLEDBConnection.CommandText ' Set objDoc = CreateObject("Msxml2.DOMDocument.4.0") Set objDoc = CreateObject("Msxml2.DOMDocument") objDoc.LoadXML sCmdText ' parse out the items we need to make the query sViewGUID = objDoc.SelectSingleNode("//*/VIEWGUID").Text sListGUID = objDoc.SelectSingleNode("//*/LISTNAME").Text sListWeb = objDoc.SelectSingleNode("//*/LISTWEB").Text Set objDoc = Nothing
End Sub '## Secondly, run the PopulateVersionInfo sub routine, this appends
' a column with the Version Number, with a date stamp in the header
Sub PopulateVersionInfo() Dim lcVersionColumn As ListColumn Dim LastColumn As Long ' get the version information GetVersionInfoFromSP ' add the column and populate Set lcVersionColumn = ActiveSheet.ListObjects(1).ListColumns.Add lcVersionColumn.DataBodyRange.Select lcVersionColumn.Range.Cells(1, 1).Value2 = "Version at " & Format(Now, "d/mm/yy hh:mm") lcVersionColumn.DataBodyRange = sarValues ' Get the end column number LastColumn = ActiveSheet.ListObjects(1).ListColumns.Count ' column fit width ActiveSheet.ListObjects(1).ListColumns(LastColumn).Range.Columns.AutoFit End Sub

Tags: sharepoint

Categories: vba

Updated: June 24, 2019

Twitter Facebook LinkedIn

Leave a Comment