Break Links not working in auto-open macro

[Ickle]

Hi,

I have the following code which works fine if run when the file is open:

It copies 2 sheets out of the source workbook and breaks links thus removing external references. However when I run this via an auto-open macro the break link doesn't work.

I would be eternally grateful if anyone could shed any light on why and what I can do about it?

ActiveWorkbook.Save
MyFile = ThisWorkbook.FullName
Sheets(Array("DENOMINATION - VOLUME SUMMARY", "DENOMINATION - VALUE SUMMARY")). _
Select
Sheets("DENOMINATION - VALUE SUMMARY").Activate
Sheets(Array("DENOMINATION - VOLUME SUMMARY", "DENOMINATION - VALUE SUMMARY")). _
Copy
ActiveWorkbook.BreakLink Name:= _
MyFile _
, Type:=xlExcelLinks

Yours in anticipation,
Ickle Lynnie

 

[Andrew Poulsom]

In which workbook is the working code? Is it the same workbook as the Autpen code? And in what way doesn't it work?

 

[Ickle]

The code is in the source workbook.

I want to open source workbook (via another program ) update external data and have it export and unlinked copy of 2 of the workbook sheets.

When I run the macro manually within the source workbook this code removes links to the source workbook.

When I call this macro from an open workbook procedure
ie Private Sub Workbook_Open()
then the code does not remove links to the source workbook.

I'm mystified!

 

[Andrew Poulsom]

How are you opening the workbook and are you sure the Workbook_Open event is firing?

Sorry I can't test at the moment because I only have Excel 2000 at the office (BreakLink is new).

 

[Ickle]

Hi,

Yes the macro is firing because it is performing the remainder of the macro fine. The difference is if I run it via the auto_open my resultant child workbook contains links to the source workbook (but exists and is right in all other respects) but if I hold shift to suppress the macro on file open and then run the macro manually then the child workbook correctly does not contain links.

I guess I could go back to the old fashioned way of doing a copy paste values on each sheet to eliminate the problem but it seems such a shame not to use the functionality when it's there!

Thanks anyway