Check if document is already open

[Alex30]

I am currently bringing in data from ofrom one document to another using the code below.

Workbooks.Open ("C:\AE\Record.xlsm")
Dim wkb As Workbook
Set wkb = Workbooks.Open("C:\AE\Record.xlsm") ' open workbook and set reference!
wkb.Sheets("Database").Activate


However, if the document is already open i get a message box telling me that i cant reopen it. Is there a way to get excel to check if the document is already open and if not open it and make the sheet "Database" the active sheet?.

My code is currently in module 4.

Regards

 

[VBA Geek]

use this UDF and pass it to the workbookname ie. Record.xlsm

Function ISWorkbookOpen(WorkbookName As String) As Boolean
On Error Resume Next
ISWorkbookOpen = Not Workbooks(WorkbookName) Is Nothing
End Function

 

[dmt32]

see if these changes to your code help:

Dim wkb As Workbook
    Dim sFileName As String, sPath As String


    sFileName = "Record.xlsm"
    sPath = "C:\AE\"


    On Error Resume Next
    Set wkb = Workbooks(sFileName)
    On Error GoTo myerror
    If wkb Is Nothing Then
        Set wkb = Workbooks.Open(sPath & sFileName, ReadOnly:=False, Password:="")    ' open workbook and set reference!
    End If
    wkb.Sheets("Database").Activate
myerror:
    If Err > 0 Then MsgBox (Error(Err)), 48, "Error"

Dave

 

[Alex30]

dmt32 - That worked great thanks, however is there a way so that if the document was already open it stays open and if it was closed it closes again?

 

[dmt32]

dmt32 - That worked great thanks, however is there a way so that if the document was already open it stays open and if it was closed it closes again?

try adding a flag & test value before Close statement. Something like this maybe:

 Dim wkb As Workbook
    Dim sFileName As String, sPath As String
    Dim FileOpened As Boolean


    sFileName = "Record.xlsm"
    sPath = "C:\AE\"


    On Error Resume Next
    Set wkb = Workbooks(sFileName)
    On Error GoTo myerror
    If wkb Is Nothing Then
        Set wkb = Workbooks.Open(sPath & sFileName, ReadOnly:=False, Password:="")    ' open workbook and set reference!
        FileOpened = True
    End If


    With wkb
         .Sheets("Database").Activate


        '
        'do stuff
        '
        If FileOpened Then .Close False 'change to True if save required
    End With

Dave