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.