Thread: Removing Links

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

All is working fine except when I open the new workbook I get asked if 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 the 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 the Update Remote References option set on when I open the new workbook and 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

Roy

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.

--
Regards,
Tom Ogilvy

"rothco" <rothco.2364pb_1139835005.2705@excelforum-nospam.com> wrote in
message news:rothco.2364pb_1139835005.2705@excelforum-nospam.com...
>
> I have a workbook and I have set up VBA code to save the workbook as a
> different workbook.
>
> All is working fine except when I open the new workbook I get asked if
> 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 the
> 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 the
> Update Remote References option set on when I open the new workbook and
> 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
>
> Roy
>
>
> --
> rothco
> ------------------------------------------------------------------------
> rothco's Profile:
http://www.excelforum.com/member.php...o&userid=31492
> View this thread: http://www.excelforum.com/showthread...hreadid=511707
>

Thanks for that

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

Roy

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.

--
Regards,
Tom Ogilvy




"rothco" <rothco.2366sb_1139837705.7581@excelforum-nospam.com> wrote in
message news:rothco.2366sb_1139837705.7581@excelforum-nospam.com...
>
> Thanks for that
>
> But how do I do it in VBA so the user does not have to manually update
> the links?
>
> Roy
>
>
> --
> rothco
> ------------------------------------------------------------------------
> rothco's Profile:
http://www.excelforum.com/member.php...o&userid=31492
> View this thread: http://www.excelforum.com/showthread...hreadid=511707
>