我有一个主 Excel 工作簿,用户可从中使用它。此 Excel 工作簿的功能是复制并使用宏等创建其他工作簿。此副本位于每个人都可以访问的网络驱动器上。问题是,如果有人将此版本复制到他们的桌面,然后我稍后想出此主工作簿的新版本,则该人复制到其桌面的旧版本将不具有宏等的最新更新。有没有办法检查或阻止旧版本运行宏或使旧工作簿正常工作?
答案1
步骤1:您可以编写一个宏来检查网络位置中的主文件。您可以使用Dir
或FSO
来执行此操作:
目录:
Sub Test_File_Exist_With_Dir()
Dim FilePath As String
Dim TestStr As String
FilePath = "\\Server\test\book1.xlsm"
TestStr = ""
On Error Resume Next
TestStr = Dir(FilePath)
On Error GoTo 0
If TestStr = "" Then
MsgBox "File doesn't exist"
Else
MsgBox "File exist"
End If
End Sub
自由太空司令部:
Sub Test_File_Exist_FSO_Late_binding()
'No need to set a reference if you use Late binding
Dim FSO As Object
Dim FilePath As String
Set FSO = CreateObject("scripting.filesystemobject")
FilePath = "\\Server\test\book1.xlsm"
If FSO.FileExists(FilePath) = False Then
MsgBox "file doesn't exist"
Else
MsgBox "File exist"
End If
End Sub
Sub Test_File_Exist_FSO_Early_binding()
'If you want to use the Intellisense help showing you the properties
'and methods of the objects as you type you can use Early binding.
'Add a reference to "Microsoft Scripting Runtime" in the VBA editor
'(Tools>References)if you want that.
Dim FSO As Scripting.FileSystemObject
Dim FilePath As String
Set FSO = New Scripting.FileSystemObject
FilePath = "\\Server\Ron\test\book1.xlsm"
If FSO.FileExists(FilePath) = False Then
MsgBox "File doesn't exist"
Else
MsgBox "File exist"
End If
End Sub
第2步:您可以让它检查该文件的最后修改日期,以确定是否存在较新的版本。
FileDateTime("\\Server\test\book1.xlsm")
示例结果:2016 年 6 月 1 日下午 7:40:18
步骤3:如果存在较新的版本,则可以向用户显示一个消息框,要求用户从网络驱动器复制新版本并关闭工作簿。(我不建议自动从网络位置复制/粘贴到用户的工作站,因为这很容易变得混乱,即使没有这个,它仍然可以完成所需的操作)
MsgBox "A new version of this file exists on the network share. Please use the new version. This workbook will now close."
ActiveWorkbook.Close savechanges:=False
参考: