打开工作簿(如果尚未打开)(如果已经打开)，然后获取该参考 [英] Open workbook if not already open, if already, then get that reference

问题描述

我有一个方案可以在另一个工作簿路径中的一个工作簿中进行一些更改.但是问题是我需要检查工作簿是否已经打开.如果不是，我需要将打开的实例保存到工作簿变量中.

Ive a scenario to do some changes in a workbook in another workbook path. But the question is I need to check whether the workbook already open or not. If not I need to get that opened instance to a workbook variable.

这里是Im用于检查工作簿是否打开的代码，然后是用于打开代码的代码

Here is the code Im using for checking whether workbook open or not and then the code for opening

Function IsFileOpen(fileFullName As String)
    Dim FileNumber As Integer
    Dim errorNum As Integer

    On Error Resume Next
    FileNumber = FreeFile()   ' Assign a free file number.
    ' Attempt to open the file and lock it.
    Open fileFullName For Input Lock Read As #FileNumber
    Close FileNumber       ' Close the file.
    errorNum = Err         ' Assign the Error Number which occured
    On Error GoTo 0        ' Turn error checking on.
    ' Now Check and see which error occurred and based
    ' on that you can decide whether file is already
    ' open
    Select Case errorNum
        ' No error occurred so ErroNum is Zero (0)
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied." is 70
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' For any other Error occurred
        Case Else
            Error errorNum
    End Select

End Function
Public Function getConsolidatedDataFile() As Workbook
    Dim p As String
    p = ActiveWorkbook.Path
    Dim cf As String
    cf = printf("{0}\ConsolidatedData.xlsx", p)
    Dim wb As Workbook
    Dim fo As Boolean
    fo = IsFileOpen(cf)
    If fo = False Then wb = Workbooks.Open(filename:=cf)
    ''I need to get the code for this place of fo is true
    getConsolidatedDataFile wb

End Function

因此，如果文件打开，则需要将该工作簿放入该wb变量中.

So if file open I need to get that workbook in to that wb variable.

推荐答案

我有一个解决方案

If fo = False Then
    Set wb = Workbooks.Open(filename:=cf)
Else
    Dim w As Workbook
    For Each w In Workbooks
        If w.FullName = cf Then
            Set wb = w
        End If
    Next
End If

这里是遍历所有工作簿的循环，如果有，则引用该引用.

Here is in the loop its traversing through all workbook and if its there take that reference..

这篇关于打开工作簿(如果尚未打开)(如果已经打开)，然后获取该参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！