Unhiding a workbook and activating it in VBA

Gratje · #1 11-05-2012, 05:48 AM

Hi,

I have a workbook (let's name it file1) with a hyperlink in one of the cells to open another workbook (file3). The filename of this file3 however changes every month. This makes it neccessary to manually edit the hyperlink in file1. I want to overcome this by making a hyperlink in file1 to file2 i.s.o. file3. In this file 2 I added a auto_open macro which determines what file3 should be opened (depending on the current month), it opens this file3, runs it auto-open macro and finally closes file2 automatically.

In principle this procedure works but with the following problem.
Upon closing file2 with its auto-open macro (since it is not needed anymore when file3 is opened), excel activates file1 and also hides file3 (this latter is the biggest problem). This makes the user unaware that file3 is actually there since it is not visible and focus is returned to file1.
What I want is that file3 becomes the active window and file1 stays in the background.

I am aware that it may be possible to add the macro from file2 to file1 and calling it with a commandbutton (then it would probably work the way I want it), but there are reasons I cannot do so (I'm not allowed to add macro's to file1).

Does anyone know how to get this to work the way I want when I click the hyperlink in file1?

BTW I'm using excel 2007
file3 always has this kind of name "file3 2012-11.xlsm"

To help make things clearer I added the auto_open macro from file2 below:

Code:

Sub auto_open()
Const pad = "C:\test\"
Dim maand As Integer
Dim jaar As Integer
Dim bestand As String
 
maand = Month(Now())
jaar = Year(Now())
 
bestand = pad + "file3 " + Format(jaar, "0000") + "-" + Format(maand, "00") + ".xlsm"
Set x = Workbooks.Open(Filename:=bestand, ReadOnly:=False)
ActiveWorkbook.RunAutoMacros xlAutoOpen 'runs macro of file3
 
Workbooks("file2.xlsm").Close savechanges:=False
End Sub

I have already tried to close file2 from the auto-open macro in file3 but the result stays the same: file1 is activated and file3 becomes hidden.

Any ideas?

Thanks for your help.

macropod · #2 11-06-2012, 05:28 AM

There is nothing in the code you have posted to indicate that 'file3' gets hidden (ie Visible:=False). Presumably the code that does that is in 'file3'.

Gratje · #3 11-06-2012, 09:07 AM

Hi Paul,
No, there is nothing in the code of file3 about changing visibility of anything. In fact when I put a hyperlink in file1 directly to file3, file3 is opened and becomes the active window. It is only when file2 is used as an intermediate file, and this file is closed that file3 becomes hidden and file1 the active window. I guess this is excel behaviour (or maybe a bug?). I can understand when 3 files are opened and the "middle" file is being closed that excel doesn't know where to go (apparently it chooses to go to file1). What I don't understand is that one of the remaining files gets hidden.
The solution I'm looking for is how can I force excel to make file3 the active window when file2 is closed. I'm confident that if I can do this, file3 doesn't get hidden.

macropod · #4 11-06-2012, 02:10 PM

Does 'file3' ever become visible when the code is running?

In any event, it seems to me you should be able to dispense with 'file2' by adding code to 'file1' to modify the hyperlink so that it always points to the correct month. You sould be able to do this if, instead of creating the hyperlink directly, you use the hyperlink function. For example:
=HYPERLINK("C:\Users\Gratje\Documents\My WorkBook Base Name"&TEXT(MONTH(TODAY()),"00")&".xlsx","C:\Users\ Gratje\Documents\My WorkBook Base Name"&TEXT(MONTH(TODAY()),"00")&".xlsx")
The above will update the hyperlink everymonth, so that, in January, it points to 'My WorkBook Base Name01', in August it points to 'My WorkBook Base Name08', etc.

The alternative is to use an Auto macro in 'file1' that updates the hyperlink each time it's opened.

Gratje · #5 11-07-2012, 05:19 AM

Hi Paul,
The option you mentiond about using the hyperlink function i.s.o. a direct hyperlink looks very promising. I ran a quick test and it looks like it is working the way I want it. With the hyperlink function, file3 is opened and becomes the activated window while file1 stays in the background. file2 is not needed this way and that would be perfect. I need to test it in the actual file where we want the link but since I don't have rights to modify this file myself, this has to wait till friday when my colleague is back. I'l let you know what the outcome will be.
So far I want to thank you for your time to help me.

Best regards,
Gerard

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
#REF! error when opening a workbook that contain a refference to another workbook	tanababa	Excel	2	06-07-2012 03:11 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook?	tinfanide	Excel Programming	1	02-09-2012 04:08 PM
Unhiding a column	Maureen	Excel	3	09-16-2011 08:57 AM
macro to transfer data from one workbook to another workbook	virsojour	Excel Programming	5	02-01-2011 08:58 PM
Select a range in one one workbook while working in other workbook	Slow&Steady	Excel	1	02-21-2010 03:34 AM