如何在没有用户提示的情况下以只读模式打开excel工作簿

Question

我使用下面的VBA代码打开一个工作簿，但是当工作簿已经被另一个用户打开时，我得到一个用户提示，询问我是否想以只读模式打开。我想直接以只读模式打开工作簿，而不需要只读用户提示符。

Sub Report()
Dim fd As FileDialog
Dim Filechosen As Boolean
Dim Savebutton As Boolean
Dim sh As Worksheet
Dim book As Workbook


Set fd = Application.FileDialog(msoFileDialogOpen)

fd.Filters.Clear
fd.Filters.Add "Old Excel Files", "*.xls"
fd.Filters.Add "New Excel Files", "*.xlsx"
fd.Filters.Add "macro Excel Files", "*.xlsm"
fd.Filters.Add "any Excel Files", "*.xl*"

fd.FilterIndex = 4

fd.AllowMultiSelect = False

fd.InitialFileName = "https:sharepoint address/BFs/"

Filechosen = fd.Show

If Not Filechosen Then
MsgBox " No File Selected"
Exit Sub
End If

fd.Execute



For Each sh In Worksheets: sh.Visible = True

Next sh


End Sub

Answer 1

当您给出打开文件的命令时，它有一个可选的命令以只读方式打开它

就写Readonly:=True

不提示用户

如果你需要进一步的说明，请告诉我

Answer 2

您可以使用VBA工作簿自己打开工作簿。从对话框中获取文件名后，打开方法。

根据需要替换您的代码。你会明白的

Dim xlFileName As String
Dim wb as Workbook
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
.AllowMultiSelect = False
.Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
If .Show Then
xlFileName = .SelectedItems(1)                   
Else
MsgBox " No File Selected"
Exit Sub
End If
End With
wb.Workbooks.Open (xlFileName), ReadOnly:=True

然后，您可以从web复制该工作簿中的工作表。工作表集合。