VBA coding help - checking version

[andrewsavvas]

I manage an Excel document that is stored on a SharePoint that approx. 60 users access each day. They are to download the file daily and run it locally.

The file has several references to other workbooks and at times I need to make changes to the references as well as regular updates. I’m finding users are not always downloading the most recent version, so I would like to create a VBA code that check for the most current file version before allowing them to open the document.

I currently have a version number and date in 'V3.6 / 27-06-2018' format in a cell in the document. So ideally I would like to compare the version number within the file and a plain text file also loaded on the SharePoint.

Where the version doesn't match, I’d like a text box to appear advising the version is outdated and for the document to force close. Where the version matches, the file would open as normal.

I am open to other suggestions if it makes it easier.

 

[kpasa]

Hey Andrew, this should do the trick.

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
   ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

Function DownloadFileFromWeb(strURL As String, strSavePath As String) As Long
   ' strSavePath includes filename
   DownloadFileFromWeb = URLDownloadToFile(0, strURL, strSavePath, 0, 0)
End Function

Sub CheckVersion()
   If DownloadFileFromWeb("https://url/to/version.txt", "C:\Path\to\Download\version.txt") = 0 Then 'File downloaded successfully
       Dim rng As Range
           Set rng = Sheets("Sheet1").Range("A1")
       If rng.Value <> TXTVersion("C:\Path\to\Download\version.txt") Then
           'Version in the workbook does not match the version from the sharepoint txt file
           'You can force the document to close here, or simply warn the user
           MsgBox "Version mismatch"
       End If
   End If
End Sub

Function TXTVersion(strFilePath)
   Dim TextFile As Integer
   Dim FilePath As String
   Dim FileContent As String

   FilePath = strFilePath
   TextFile = FreeFile
   Open FilePath For Input As TextFile
   FileContent = Input(LOF(TextFile), TextFile)
   TXTVersion = FileContent
   Close TextFile
End Function

The checkversion sub first downloads the public txt file from the specified URL to a specified folder (since this document is used by multiple people, I recommend setting the download location to the same directory as the workbook itself:

Application.ActiveWorkbook.Path

If the download is successful (the function returns a 0 if the file download was successful), then it opens the files (again, you need to specify where you just downloaded it to), reads the version number, and tests if it matches the version located in a cell within the document. if it does not match, you can then force a message to the user to download the new document and close the current document. You could even prompt the user to go to the download page by using this macro to open a web URL:

Sub OpenUrl(vbURL As String)
    Dim lSuccess As Long
    lSuccess = ShellExecute(0, "Open", vbURL)
End Sub

To wrap it up, I'd recommend running the checkversion from the On Open event. That way, it runs without the user being able to bypass the check (unless they disable macros).

 

[andrewsavvas]

Thank you kpasa - after a little tweaking it has worked a treat.