Code snippet for those of you that release updates to Excel Macros

[Blade Hunter]

Dimensionalise this at a global level:

Global Const RepVer As String = "1.1"


At the Workbook level:

Private Sub Workbook_Open()
Call CheckVer
If CurrentRepVer <> RepVer Then
    If MsgBox("Latest version on server = " & CurrentRepVer & " This version = " & RepVer & Chr(13) & Chr(13) & "You need to either copy the latest version from: ""<LOCATION file of>"" or set up a shortcut to this file. Would you like me to copy the latest version for you?" & _
    Chr(13) & Chr(13) & "It is highly recommended that you set up a shortcut to this file rather than saving a local copy to ensure you always use the latest version.", vbYesNo, "Version conflict detected") = vbYes Then
        Call CopyNewVersion
    End If
End If
End Sub

In a module:

Sub CopyNewVersion()
Dim OldWorkBookDIR As String
Dim OldWorkBook As String
Dim NewWorkBookDIR As String
Dim NewWorkBook As String
NewWorkBookDIR = "dir of new workbook"
NewWorkBook = "name of new workbook"
OldWorkBookDIR = ThisWorkbook.Path & "\"
OldWorkBook = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "_OLD_" & Format(Now(), "YYYYMMDD") & ".xls" 'Formats to backup the old one including the date.
ThisWorkbook.SaveAs (OldWorkBook)
FileCopy NewWorkBookDIR & NewWorkBook, OldWorkBookDIR & NewWorkBook
Workbooks.Open OldWorkBookDIR & NewWorkBook
Workbooks(OldWorkBook).Activate
ActiveWorkbook.Close False
End Sub

Sub CheckVer()
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
    Call Get_User
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=;" & DBSource & _
    "USER ID=" & DatabaseUserName & ";PASSWORD=" & DatabasePassword
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = "SQL Code to retrieve version from the database<SQLCODE the there stored have you that database from number Version a retrieve to>"
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    Do While Not RS.EOF
       Row = Row + 1
       For Findex = 0 To RS.Fields.Count - 1
         CurrentRepVer = RS.Fields(Findex).Value
       Next Findex
       RS.MoveNext
    Loop
End Sub

I hope this helps someone. I am using it on an app that people are already using but I am continuing development of. This way even if someone copies it to their local rather than running from the host file, it will update whenever I push out a new version .

All you do is change your global RepVer to a new version and update the version on the database to match it and voila

Cheers

Dan

 

[wrstlngkayak]

Dan,

Thanks for posting that code. Excuse my inexperience, but I'm not sure how or if i can apply this to my marco. I want to do the same task you described but I'm not sure we are working with the same things. Right now i have that add-in file saved to a shared file server that my department has access to. Each person in my group has copied that file to the add-in directory on their pc. What i would like to do is be able to save a new version of the macro to the shared drive on the server and have the macro update the next time each user runs it. I have no knowledge of SQL databases and it seems your code makes use of those. Is it possible to do the task i am suggesting and if so do you know how i could do it without using SQL?

Thanks,

Adam

 

[Blade Hunter]

Sure, I did it this way as I like as much as possible to be server based .

So what you could do would be save a spreadsheet somewhere (Call it something like VersionControl.xls) and simply put in cell A1 a version number.

Put a version in your code as I have above and instead of hitting the server for the version number, simply hit VersionControl.xls on range A1. Populate that into the variable CurrentRepVer. The code to do this part should completely replace the third subroutine I posted in my original post. The logic for the other routines should stay the same as it is just a comparison of two strings to see if they are the same, if they are not then it will perform the action.

You have noted that your is an add-in. I have no experience with XLA files, Hopefuly someone else on here will hop in and comment on if this method will work for an XLA aswell. Not sure if you have to deactivate it before closing. If you know how to do this part then cool but if not, drop me a line and I will see what I can work out for you.

Cheers

Dan