Auto open a diff. workbook, then hide it

Microsoft Excel 2003 (full product)
March 16, 2010 at 10:26:12
Specs: Windows XP
Workbook 1 uses data (for a dropdown) from Workbook 2. In order for workbook 1 to be able to pull the data, both workbooks need to be open. Is there some setting to tell Excel, "When workbook 1 is opened, also open workbook 2 and hide it." Just like what happens to the Personal.xls file...Maybe a macro?
Also, I can't save it the Excel start folder because the file is accessed from multiple computers. Workbook 1 is saved on the network and multiple people will use it so this setting needs to work regardless of what computer is used. Any ideas?

See More: Auto open a diff. workbook, then hide it

Report •


#1
March 16, 2010 at 10:45:39
You can create a Macro that uses the Open event.

1 - In Workbook 1, use Alt-F11 to open the VBA editor.

2 - Double click the ThisWorkbook module and put something like this in the pane:

Private Sub Workbook_Open()
  Workbooks.Open Filename:= "W:\MyWorkbook2.xls"
End Sub

3 - Open Workbook2 and Hide it.
4 - Exit Excel, clicking Yes to Save Workbook1 & 2

The next time you open Workbook1, Workbook2 should open Hidden.


Report •

#2
March 16, 2010 at 11:35:10
Is there a way to have this macro run upon opening? I did what you said (opened #2 then hid, saved, then closed out everything) but when I re-opened WB one, I had to run the macro in order for the other book to open then hide. How do I tell it to run the macro everything the book is opened?

Report •

#3
March 16, 2010 at 12:42:50
Did you follow this step:

2 - Double click the ThisWorkbook module and put something like this in the pane

Workbook_Open code has to go in the ThisWorkbook module in order to run as soon as the workbook is opened.

Works for me...


Report •

Related Solutions

#4
March 16, 2010 at 12:49:14
You're right, I put it in the wrong place, works great! How do I get workbook 2 to Auto save when closing? I don't want anyone to know it's even open but it asks about saving BOTH books when workbook one is closed.... Any idea?

Report •

#5
March 16, 2010 at 14:39:54
Why would it ask about saving Workbook2 if it's hidden? What changes are being made?

Do you have volatile functions in it? e.g. TODAY(), NOW()


Report •

#6
March 16, 2010 at 15:06:37
Not sure why, no changes are being made to it... Wrote a macro to autosave and fixed the problem. New question, How do I allow multiple uses to access this hidden file without them knowing about it? For example, Workbooks 2,3,4,5 all link to workbook 1 (which is hidden). When I go to open workbook 2 and 3, I get the message informing me that (paraphrased) "my hidden workbook is already open, do I want to re-open and discard changes?" The people using these books are not Excel savvy and will not undertand why these messages appear.... Have any suggestions on how to fix it? AND, if I'm in any of the workbooks (with the hidden 1 open) and someone else tries to acces a different one, their file will also try to open the hidden workbook 1, so they'll get a read only message...

Report •

#7
March 16, 2010 at 15:37:33
re: Not sure why, no changes are being made to it... Wrote a macro to autosave and fixed the problem.

Maybe it's just me, but if Excel kept asking me to save a workbook where no changes were being made, I wouldn't mask the situation with a macro. I'd dig into the issue and make sure I understood what was going on.

re: When I go to open workbook 2 and 3, I get the message informing me that (paraphrased) "my hidden workbook is already open, do I want to re-open and discard changes?"

Private Sub Workbook_Open()
   For Each book In Application.Workbooks
    If book.Name = "Book1.xls" Then Exit Sub
   Next
    Workbooks.Open Filename:="C:\Documents and Settings\Me\Desktop\Book1.xls"
End Sub


Report •


Ask Question