asked on

auto open excel workbook

Hi
I have muliple workbooks in the same folder.
One master workbook is referenced by several of the other workbooks with data flowing both ways between the master and the dependents.
I would like the master workbook to be opened automatically whenever any of the dependent workbooks are opened.
The master workbook and the dependent/s to remain open until the operator manually closes/saves them.
Thanks
David

redmondb

David,

You'd need an Auto_Open (or Workbook_Open) macro in each of the dependent workbooks. Is that acceptable? Code would be as simple as the following...

Sub Auto_Open()

Workbooks.Open ("D:\Master.xlsx")

End Sub

Open in new window

Regards,
Brian.

DAVID131

ASKER

Thanks Brian

Very simple answer but the folder is portable and could be sitting in any one of a number of drives so I could not hard code in "D:
Can I get around this by somehow coding in that the filepath is the same as the open workbook?

David

ASKER CERTIFIED SOLUTION

redmondb

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DAVID131

ASKER

Thanks Brian
Works a treat
David

redmondb

Thanks, David.

redmondb

David,

Oops, if the master is already open, you don't want an attempt to re-open it, so...

Option Explicit

Sub Auto_Open()

If Book_Exists("Master.xlsx") Then Exit Sub

Workbooks.Open (ThisWorkbook.Path & "\" & "Master.xlsx")

End Sub

Function Book_Exists(xBook_Name As String) As Boolean

Book_Exists = False

On Error Resume Next
    Book_Exists = (Workbooks(xBook_Name).Name = xBook_Name)
On Error GoTo 0

End Function

Open in new window

Regards,
Brian.

DAVID131

ASKER

Thanks Brian
Had not thought about that!
David

redmondb

All part of the (belated!) service, David.