VBA打开工作簿错误?

Question

我正在使用 vba 尝试打开工作簿(如果尚未打开)。

我遇到的问题是有时工作簿可以由其他用户打开，因此如果工作簿被锁定，那么我想为用户提供一个以只读方式打开工作簿的选项。

法典：

'Open Planner
On Error Resume Next
Set WB = Workbooks("2017 Planner.xlsx")
On Error GoTo 0
If WB Is Nothing Then 'open workbook if not open
On Error GoTo Message4
Set WB = Workbooks.Open("G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx", Password:="samples", WriteResPassword:="samples", UpdateLinks:=False)
Message4:
Dim answer2 As Integer
answer2 = MsgBox("Oooops!" & vbNewLine & vbNewLine & "We had trouble opening the planner with Read/Write access. We can open the file in Read-Only but this means the planner won't automatically be updated. Would you like to continue?", vbYesNo + vbQuestion, "Notice")
If answer2 = vbNo Then
Exit Sub
Else
Set WB = Workbooks.Open("G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx", ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
End If
End If

由于某种原因，我在此行收到错误 1004：

Set WB = Workbooks.Open("G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx", ReadOnly:=True, IgnoreReadOnlyRecommended:=True)

Answer 1

只是为了检查，尝试将文件放在某个没有任何特殊字符的目录中。

即：C：\工作簿

确保打开文件的权限。

Answer 2

您可以测试它是否已经打开：

Sub Test()
Dim sFilePath As String
Dim wrkBk As Workbook
sFilePath = "G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx"
If WorkBookIsOpen(sFilePath) Then
Set wrkBk = Workbooks.Open(sFilePath, ReadOnly:=True)
Else
Set wrkBk = Workbooks.Open(sFilePath)
End If
End Sub
Public Function WorkBookIsOpen(FullFilePath As String) As Boolean
Dim ff As Long
On Error Resume Next
ff = FreeFile()
Open FullFilePath For Input Lock Read As #ff
Close ff
WorkBookIsOpen = (Err.Number <> 0)
On Error GoTo 0
End Function

由于函数WorkBookIsOpen返回布尔值，而ReadOnly属性需要布尔值，因此您可以使用以下较短的过程：

Sub Test2()
Dim sFilePath As String
Dim wrkBk As Workbook
sFilePath = "G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx"
Set wrkBk = Workbooks.Open(sFilePath, ReadOnly:=WorkBookIsOpen(sFilePath))
End Sub

Answer 3

试试这个：

dim link as string
link= "G:BUYINGFood Specials2. Planning1. Planning1. Planner8. 20172017 Planner.xlsx"
Set wb = Workbooks.Open(Filename:=link, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)