BreakLink

[steve]

See the code below. I want to copy a sheet named "Report" to a new workbook.
Then I want to break all the links. I am gettin an error that says
"BreakLink of workbook failed" or something like that. any ideas?

Dim Nwb as Workbook
Set Nwb = Workbooks.Add

With Nwb
ThisWorkbook.Worksheets("Report").Copy after:=.Worksheet _
(.Worksheets.Count)
Dim LinkSource As String
LinkSource = ThisWorkbook.Path & ThisWorkbook.Name
.BreakLink Name:=LinkSource, Type:=xlLinkTypeExcelLinks
End With

 

[Dave Peterson]

Your code worked for me--after I fixed the typo
(.worksheetS(.worksheets.count)).

You may want to paste the actual portion of the code that failed.

And while you're testing links, make sure you have a copy of Bill Manville's
FindLink program:
http://www.oaltd.co.uk/MVP/Default.htm

You'll find it very useful.

 

[steve]

the "s" was lost when i copied it over. here is the exact error I keep
getting:

Method 'BreakLink' of Object '_Workbook' failed

thanks for the link, but I still can't figure out why i'm getting an error
here.

 

[Dave Peterson]

I could get the error if there were no links or if the worksheet were protected.

If you do it manually, does that provide any hint?

 

[steve]

Dave,

I'm sorry. I forgot the "\" in the LinkSource string!!

 

[NickHK]

Steve,
Which version of Excel do you have ?
Not supported in XL2K or before.

NickHK

 

[Ron de Bruin]

I think it is this Dave the /

LinkSource = ThisWorkbook.Path & "/" & ThisWorkbook.Name

 

[Dave Peterson]

And when I tested, I hadn't saved the workbook--so the path was "".

Glad you got it working.

 

[Dave Peterson]

Good eyes, Ron.

I think it is this Dave the /

LinkSource = ThisWorkbook.Path & "/" & ThisWorkbook.Name