Removing Links
 

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

--
rothc
-----------------------------------------------------------------------
rothco's Profile: http://www.excelforum.com/member.php...fo&userid=3149
View this thread: http://www.excelforum.com/showthread.php?threadid=51170

Removing Links
 

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" wrote in
message ...

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

Removing Links
 

Thanks for that

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

Ro

--
rothc
-----------------------------------------------------------------------
rothco's Profile: http://www.excelforum.com/member.php...fo&userid=3149
View this thread: http://www.excelforum.com/showthread.php?threadid=51170

Removing Links
 

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" wrote in
message ...

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