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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

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
7,337
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
7,337
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,214
Messages
5,836,045
Members
430,402
Latest member
bcurran2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top