vba 检查工作簿是否存在，如果存在则检查它是否打开。如果打开则激活，如果关闭则打开它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28449308/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if workbook exists, if yes then check if its open. if open then activate, if closed then open it
提问by Manya Mohan
I am working on a VBA program where I need to do following:
我正在开发一个 VBA 程序，我需要执行以下操作：
When the button is clicked (the macro is run):
单击按钮时（运行宏）：
Check if the MS EXCEL workbook exists in the folder. If not, then give a message that 'workbook does not exist' and VBA program should end.
If workbook exists, then check if workbook closed or open. If its closed, then open the workbook and VBA program should move with fruther steps.
If the sheet is open then activate the workbook and VBA program should move with fruther steps.
检查文件夹中是否存在 MS EXCEL 工作簿。如果没有，则给出“工作簿不存在”的消息，VBA 程序应该结束。
如果工作簿存在，则检查工作簿是关闭还是打开。如果它已关闭，则打开工作簿，VBA 程序应该以更进一步的步骤运行。
如果工作表已打开，则激活工作簿，VBA 程序应以更进一步的步骤运行。
I have written this so far but its not working:
到目前为止我已经写了这个，但它不起作用：
Sub test()
Dim WbookCheck As Workbook
On Error Resume Next
Set WbookCheck = Workbooks("Weekly Report.xls")
On Error GoTo 0
filepaths = "c:\clients\work\Weekly Report.xls"
If Dir("filepaths") = False Then
MsgBox "Please save the latest file under the name 'US Sector Flow Weekly Report' and run the macro again"
Exit Sub
ElseIf WbookCheck Is Nothing Then
Workbooks.Open "c:\clients\work\Weekly Report.xls"
Else
WbookCheck.Activate
End If
Workbooks("Weekly Report.xls").Activate
Sheets("This week").Select
Sheets("This week").Copy Before:=Workbooks( _
"Consolidated.xls").Sheets(1)
End Sub
回答by Tim Williams
Sub test()
Dim WbookCheck As Workbook
On Error Resume Next
Set WbookCheck = Workbooks("Weekly Report.xls")
On Error GoTo 0
If WbookCheck Is Nothing then 'not open....
filepaths = "c:\clients\work\Weekly Report.xls"
If Dir(filepaths) = "" Then
MsgBox "Please save the latest file under the name" & _
" 'US Sector Flow Weekly Report' and run the macro again"
Exit Sub
Else
'file exists - open it
Set WbookCheck = Workbooks.Open(filepaths)
End If
End If
with WbookCheck
.Activate
.Sheets("This week").Copy _
Before:=Workbooks("Consolidated.xls").Sheets(1)
end with
End Sub
