Removing Links

[rothco]

I have a workbook and I have set up VBA code to save the workbook as
different workbook.

All is working fine except when I open the new workbook I get asked i
I want to update the links to the old workbook.

I want to break this link when I write my new workbook so I set th
save code to be:

With newBook
.UpdateRemoteReferences = False
.SaveLinkValues = False
.SaveAs Filename:=strFileName
.Close
End With

Trouble is this doesn't seem to work. My new workbook still has th
Update Remote References option set on when I open the new workbook an
I get asked if I want to update the references.

I want to save the book without the links being active.

What am I doing wrong?

Thanks in advance

Ro

 

[Tom Ogilvy]

Open the workbook and update links

Then go to Edit=>Links

Select the like to the other workbook and select change source. Point it to
your current workbook/change the source to the current workbook. .

Save the file.

 

[rothco]

Thanks for that

But how do I do it in VBA so the user does not have to manually updat
the links?

Ro

 

[Tom Ogilvy]

The best would be to avoid the problem.

Without knowing how you induced the links, it is hard to say how to avoid
it, but one way would be to make a copy of the complete file, then remove
what you don't want in the new file.

ActiveWorkbook.SaveCopyAs "newname.xls"
workbooks.Open NewName.xls
Application.DisplayAlerts = False
activeworkbook.Worksheets(3).Delete
Application.DisplayAlerts = True


Or copy all the sheets you want to copy at one time

Worksheets(Array("Sheet1","Sheet3","sheet5")).Copy

as long as they don't refer to any sheets that were not copies, then the
links would not be created.

Otherwise, turn on the macro recorder while you change the source of the
links and use that code as your start.