检查工作簿是否存在，如果是，则检查其是否打开.如果打开则激活，如果关闭则打开 [英] Check if workbook exists, if yes then check if its open. if open then activate, if closed then open it

问题描述

我正在研究VBA程序，需要执行以下操作:

I am working on a VBA program where I need to do following:

单击按钮(运行宏)时:

When the button is clicked (the macro is run):

1. 检查文件夹中是否存在MS EXCEL工作簿.如果没有，则提示工作簿不存在"，并且VBA程序应结束.

1. 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.

如果工作簿存在，则检查工作簿是否关闭或打开.如果已关闭，则打开工作簿，然后VBA程序应以更进一步的步骤移动.

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.

如果工作表已打开，请激活工作簿，然后VBA程序应以更进一步的步骤移动.

If the sheet is open then activate the workbook and VBA program should move with fruther steps.

到目前为止，我已经写了这篇文章，但是没有用:

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

推荐答案

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

这篇关于检查工作簿是否存在，如果是，则检查其是否打开.如果打开则激活，如果关闭则打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！