How to include/retain the document version with “Export to Excel” (Spreadsheet)

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 as expected.

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.

This is the code:

Sub PopulateVersionInfo() reads the version info from the SharePoint
Sub PopulateVersionInfo() inserts the version-info into the Excel-file

 Option Explicit
Dim sViewGUID As String
Dim sListGUID As String
Dim sListWeb As String
Dim sarValues() As String

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

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 PopulateVersionInfo()
    
    Dim lcVersionColumn As ListColumn
    
    '' 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"
    lcVersionColumn.DataBodyRange = sarValues
    
End Sub 

In your Excel file with the connection to your SharePoint Document Library, first run the " Sub GetVersionInfoFromSP()". The version-info from the SharePoint Document Library will be read into an array. Secondly, run the " Sub PopulateVersionInfo()". This code will insert the version-info into your Excel file.

One important note: we are creating some objects from external classes here; Msxml2. The code will fail if msxml2 is not available on the system.

If you receive a compile error in the line:

     Dim objDOC As New MSXML2.DOMDocument40

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

-> [HKEY_CLASSES_ROOT\Msxml2.DOMDocument.4.0]

-> [HKEY_CLASSES_ROOT\Msxml2.DOMDocument.4.0\CLSID]

-> [HKEY_CLASSES_ROOT\Msxml2.DOMDocument.4.0\CLSID\(Default) = {88D969C0-F192-11D4-A65F-0040963251E5}]

In this case, please download and install the MSXML 4.0 pack from  the following source:

https://www.microsoft.com/downloads/en/details.aspx?FamilyID=7f6c0cb4-7a5e-4790-a7cf-9e139e6819c0

If these registry keys do exist and/or MSXML4.0 has been installed already, it’s most likely that reference to the MSXML.DLL is missing. In order to fix this, go in the VBA-editor, point to TOOLS -> REFERENCES and add a reference to "Microsoft XML, v.4.0".

If for some reason the problem still occurs and Excel still has problems to reference the XML-library, use LateBinding instead of EarlyBinding. This might be even a better solution since different machines could have different versions of MSXML.

To do this replace the code line:

Dim objDoc As New MSXML2.DOMDocument40

With:

Dim objDoc

Please make sure to first test this carefully in your test environment. This code is “as-is” and we do not provide support on this custom code.

Happy Exporting

Thanks to Ina from the Office Team.