2

Using Windows 10 (Build 1903 if that's relevant?) and 64bit Office 365 (probably relevant?) I've implemented a system that allows me to version control Excel VBA code.

I'm using the Workbook_BeforeSave method to check whether the current file is saved or not, and if it is saved, where it is saved to.

This works fine and will prompt the user as to whether they want to update the code contained within. I then thought that maybe I should in fact check if the code "needs" to be updated prior to prompting the user.

First off, I found the following question/solution: Using VBA to read the metadata or file properties of files in a SharePoint doc library

which I couldn't use without DSOFile.dll that I was able to install from here:

https://www.microsoft.com/en-us/download/details.aspx?id=8422

Here follows the code I have which doesn't work:

Private Function CheckTemplateIsNewerThanCurrentFile(ByVal templatePath As String) As Boolean
    Dim templateName As String
    Dim fso As New FileSystemObject
    templateName = ActiveWorkbook.CustomDocumentProperties("TemplateName").Value
    If fso.FileExists(templatePath & "\" & LocalTemplateName) Then
        Dim objDSO As New DSOFile.OleDocumentProperties
        objDSO.Open templatePath & "\" & LocalTemplateName, True, dsoOptionDefault
        If Not objDSO.CustomProperties("LastCommitDate") = ActiveDocument.CustomDocumentProperties("LastCommitDate").Value Then
            CheckTemplateIsNewerThanCurrentFile = False
        Else
            CheckTemplateIsNewerThanCurrentFile = True
            TemplateLastCommitDate = objDSO.CustomProperties.Item("LastCommitDate")
        End If
    End If
End Function

And here (highlighted) is the error I receive trying to run the method above on an .xlsb file:

Error message

(FWIW: the reason for use of the .xlsb format is because we're working with 500K+ rows of data in the process we're carrying out. Yes, I know Excel is ABSOLUTELY NOT the tool for this but we're lumbered with it now)

I know I could have already tried changing the file format to .xlsm but because this file is version controlled that is a pain to do if the method is still likely to fail.

Thanks in advance,

Alex.

AlexFielder
  • 137
  • 1
  • 12
  • I _think_ in this case, I can actually answer my own question (Yay!). During my testing I had forgotten that I had the local template open, so when objDSO attempted to open it, the file was already open in Excel so objDSO was unable to access it. The error message is I suppose related to that? – AlexFielder Nov 11 '19 at 13:22

0 Answers0