kill old versions of workbook

[ward376]

I need to update a workbook periodically and would like to find and
remove old versions of the workbook, on any drive the user has access
to when they open this new file. I just want this code to execute the
first time the workbook is opened by a particular user. I'll be sending
the workbook as an email attachment and have it available on a shared
drive. The workbooks are named similarly; workbookv1, workbookv2, etc.

 

[Tim Williams]

Deleting workbooks automatically sounds like a recipe for unwanted consequences...

A better approach might be to place code in the workbook which checks on opening to see if it's still current (for example by
checking in a shared network folder to see if the version has been updated). If the workbook being opened is not the latest version
then warn the user that there is a newer version available (and give the location) and ask they use that instead.


Eg:
'*****************************************************
Private Sub Workbook_Open()

Const CURRENT_VERSION As Integer = 3 'update in new version
Const FILE_BASENAME As String = "ExcelTool"
Const FILE_BASEPATH As String = "\\someshareddrive\folder\"

Dim sFile As String

sFile = FILE_BASEPATH & FILE_BASENAME & "_v" & _
CStr(CURRENT_VERSION) & ".xls"

If Dir(sFile) = "" Then
'this file is no longer current
MsgBox "A new version (" & CURRENT_VERSION & ") of this file " & _
" is available at " & _
vbCrLf & FILE_BASEPATH & vbCrLf & _
"Please use the new version."

End If
End Sub
'*******************************************************

 

[ward376]

Thanks!