[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

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
0
DAVID131
Asked:
DAVID131
  • 5
  • 3
1 Solution
 
redmondbCommented:
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.
0
 
DAVID131Author Commented:
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
0
 
redmondbCommented:
David,

Sure...
Sub Auto_Open()

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

End Sub

Open in new window

Regards,
Brian.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DAVID131Author Commented:
Thanks Brian
Works a treat
David
0
 
redmondbCommented:
Thanks, David.
0
 
redmondbCommented:
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.
0
 
DAVID131Author Commented:
Thanks Brian
Had not thought about that!
David
0
 
redmondbCommented:
All part of the (belated!) service, David.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now