Check if document is already open

Alex30

Board Regular
Joined
Jun 16, 2014
Messages
50
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
use this UDF and pass it to the workbookname ie. Record.xlsm

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,095
Office Version
  1. 2019
Platform
  1. Windows
see if these changes to your code help:

Code:
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

Board Regular
Joined
Jun 16, 2014
Messages
50
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

Well-known Member
Joined
Jul 3, 2012
Messages
6,095
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
 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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,044
Messages
5,526,439
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top