Automatically open external workbook with Template

  Ben Avery 15:42 18 Oct 04
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 04

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 04

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 04

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 04

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 04

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 04

where does that go into the script?

  pc moron 16:50 18 Oct 04

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 04

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 04

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 04

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…