Thread: Linking Workbooks?

Hi

You'd need to make a record of when the client file was first downloaded. Then compare that date to the modified date of the master file when the user has access to the network drive.

The following code does the following when the file is opened:

1) If Thisworkbook is not the master file (in this case the master file is "E:\Excel VBA\Project File.xlsm") then it records the date and time in Cell A1 on Sheet1 if that cell is empty. This date becomes the date that the client file was first downloaded from the network.

2) If the user has access to the E drive then it will check if the master file is newer than the date in cell A1. If it is, the user will be shown a msgbox asking them to download the newer master file.

Automatically downloading the new file for the user will be difficult because (I'm assuming) the file names of the client and master files will be the same.....

You'll need to customise these two variables to suit your needs:

Set varDLdate = Sheets("Sheet1").Range("A1")
varWBmasterStr = "E:\Excel VBA\Project File.xlsm"

Sub Auto_Open()

On Error Resume Next

Dim varDLdate As Range
Dim varWBmasterStr As String
Dim varWBclientStr As String
Dim varWBmasterModDate As Date
Dim varWBclientModDate As Date
Dim varWBclientDLDate As Date

Set varDLdate = Sheets("Sheet1").Range("A1")
varWBmasterStr = "E:\Excel VBA\Project File.xlsm"
varWBclientStr = ThisWorkbook.FullName

'Exit if the current workbook is the master file
If Left$(varWBclientStr, 1) = Left$(varWBmasterStr, 1) Then
    varDLdate.Value = ""
    Exit Sub
End If

'Record the date if the download date cell is empty
If varDLdate.Value = "" Then
    varDLdate.Value = Now
End If

'Check if master file exists and get modified dates
If Dir(varWBmasterStr) = "" Then
    'Do nothing (the master file does not exist or the user isn't on the network)
Else
    'Get the modified date (for master file) and download date (for client file)
    varWBmasterModDate = FileLastModified(varWBmasterStr)
    varWBclientModDate = FileLastModified(varWBclientStr)
    varWBclientDLDate = varDLdate.Value
    
    If varWBmasterModDate > varWBclientDLDate Or varWBmasterModDate > varWBclientModDate Then
        MsgBox "An updated version of the file exists." & _
        vbNewLine & vbNewLine & "Please download the latest version from:" & _
        vbNewLine & vbNewLine & varWBmasterStr
    End If
End If

End Sub

Function FileLastModified(strFullFileName As String)
Dim fs As Object, f As Object, s As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFullFileName)

s = f.DateLastModified
FileLastModified = s

Set fs = Nothing: Set f = Nothing

End Function

Did you know ?

c01=FileDatetime("E:\Of\simple.xls")

it does exactly the same thing as

Function FileLastModified(strFullFileName As String)
Dim fs As Object, f As Object, s As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(strFullFileName)

s = f.DateLastModified
FileLastModified = s

Set fs = Nothing: Set f = Nothing

End Function

It's worth while to press on F2 in the VBEditor to study all the methods in the VBA-library. Do not invent what has been built-in.