Automatically open external workbook with Template

  Ben Avery 15:42 18 Oct 2004
Locked

G'day

I have an Excel template which is linked to data in another workbook (source/destination). My problem is this:

When I open the destination file, if the source file is not already open, the links do not work and return errors in the linked cells (which use the IF and OFFSET commands). If the book is open links are fine, but not if it isn't.

What I really want to do is set the template (destination) file up to automatically open up the source file on opening. It works if I set it up to open all the time, but of course, this makes it a drag to open new blank workbooks up.

Any help please?

BA

  pc moron 15:56 18 Oct 2004

Copy and paste this into the ThisWorkbook module in the VB Editor.


Private Sub Workbook_Open()


Workbooks.Open Filename:="Path to the file you want to open"


End Sub

The quotes are required.

  Ben Avery 16:15 18 Oct 2004

Ok, that opens the workbook but the links still do not work. I'm assuming this is because Excel looks for the links prior to actually opening the workbook and the VB kicks in afterwards?

  Ben Avery 16:18 18 Oct 2004

Is there a simple way to change the file location in the EDIT>LINKS location as it seems to be defaulting itself to "C:/Documents and Settings..." where in reality the file is located on a Server and in the same folder (I assumer this means I don't need a full path, just the filename.xls bit?)

  Ben Avery 16:26 18 Oct 2004

I've unprotected the sheet and updated the location to the server, which works fine on my machine (XP-Pro, Office XP 2002)...however, on a seperate machine (XP-Pro, Office 2003) the file does not open and the updates do not work.

Is that a little more help?

  cherria 16:34 18 Oct 2004

I think you need this in the VB as well

ActiveWorkbook.UpdateLink Name:="path and filename", Type:= xlExcelLinks

this will force the links to update.

Failing that, you may just need to force a recalculation.

ActiveWorkbook.Calculate

  Ben Avery 16:44 18 Oct 2004

where does that go into the script?

  pc moron 16:50 18 Oct 2004

I don't know why it works on your machine and not on the Office 2003 machine.

If you can open the file on the Office 2003 machine and the links don't work, then check the following:

Tools> Options> Calculation tab.

Make sure Update Remote References and Save External Link Values are ticked.

  Ben Avery 16:57 18 Oct 2004

on the 2003 machine the folder is mapped as a network drive and so changes from //SERVERNAME to H://...

Is this the reason and how do I get around it.

Both boxes are ticked btw.

  pc moron 17:02 18 Oct 2004

It shouldn't make any difference- if the FULL path is defined, the file should open.

Here's something to try:

Set a macro going to record your actions and attempt to open the file manually on the 2003 machine- see what it records for the path.

  Ben Avery 09:14 19 Oct 2004

Ok, this is just wierd, if I open the source file 1st, then open the destination file the links work fine, although I get an error because the macro is trying to open a file already open.

That's just strange!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone