Macro to Break Links Before Saving Sheet

[ryguy7272]

I tried to come up with a method to create a 'tear off sheet', which would
basically be a single summary sheet that has all relevant data for a series
of complicated calculations that are done with an Excel model. I tried the
following with the macro recorder on: right-click, copy sheet, New Book,
create a copy. Then, I saved the single worksheet to my C-drive. The
problem is that when the model recalculates, all the new data flows through
to the Summary sheet that I just created. In many instances this would be
perfectly fine, but in this instance, it is not fine because I want the
Summary sheet to be a static result of the analysis that was done on a
certain date at a certain time; I don't want the Summary sheet that is saved
to be updated each time the model is rerun because I will save a different
Summary sheet, with a different name, each time the model is run again.

Below is the code that I have now, but for some reason it doesn't allow me
to break the links between the model and the Summary sheet, when the Summary
sheet is saved.

Sheets("Summary").Select
Sheets("Summary").Copy
ActiveWorkbook.BreakLink Name:= _
"C:\Documents and Settings\laptop\Desktop\Reporting.xls" _
, Type:=xlExcelLinks
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\" & strFilename,
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

On Error Resume Next

Does anyone know how to use code to break those links to a single worksheet,
saved on the local drive, just before it is actually saved?

Regards,
Ryan---

 

[Jim Cone]

I haven't used the BreakLink method - why not just copy and paste values?

However, reviewing BreakLink in the help file reveals that "Name" should
refer to the name of the link. Also, the LinkSources method for a workbook
returns an array of all of the link names in the workbook...
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ryguy7272"
<[email protected]>
wrote in message
I tried to come up with a method to create a 'tear off sheet', which would
basically be a single summary sheet that has all relevant data for a series
of complicated calculations that are done with an Excel model. I tried the
following with the macro recorder on: right-click, copy sheet, New Book,
create a copy. Then, I saved the single worksheet to my C-drive. The
problem is that when the model recalculates, all the new data flows through
to the Summary sheet that I just created. In many instances this would be
perfectly fine, but in this instance, it is not fine because I want the
Summary sheet to be a static result of the analysis that was done on a
certain date at a certain time; I don't want the Summary sheet that is saved
to be updated each time the model is rerun because I will save a different
Summary sheet, with a different name, each time the model is run again.

Below is the code that I have now, but for some reason it doesn't allow me
to break the links between the model and the Summary sheet, when the Summary
sheet is saved.

Sheets("Summary").Select
Sheets("Summary").Copy
ActiveWorkbook.BreakLink Name:= _
"C:\Documents and Settings\laptop\Desktop\Reporting.xls" _
, Type:=xlExcelLinks
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\" & strFilename,
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
On Error Resume Next

Does anyone know how to use code to break those links to a single worksheet,
saved on the local drive, just before it is actually saved?
Regards,
Ryan---

 

[ryguy7272]

Well, because of the formatting, everything gets really screwed up if I try
to copy and paste to a new sheet. Also, there are some merged cells, and I
encountered some problems when I tried to paste the Summary sheet it to a new
worksheet. then right-click and create new sheet method works well, but I
have all those links in there and every time I rerun the model all the data
in the linked files gets updated...but it shouldn't be updated!! Can anyone
think of any other workarounds? Or, can anyone think of a way to use VBA to
break those links?

Regards,
Ryan---

 

[Jim Cone]

Did you look at the help file as I suggested?
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"ryguy7272" <[email protected]>
wrote in message
Well, because of the formatting, everything gets really screwed up if I try
to copy and paste to a new sheet. Also, there are some merged cells, and I
encountered some problems when I tried to paste the Summary sheet it to a new
worksheet. then right-click and create new sheet method works well, but I
have all those links in there and every time I rerun the model all the data
in the linked files gets updated...but it shouldn't be updated!! Can anyone
think of any other workarounds? Or, can anyone think of a way to use VBA to
break those links?
Regards,
Ryan---

 

[ryguy7272]

I just looked at the help menu right now.
I think that makes sense. I'll try a few ideas later.

Thanks for the look,
Ryan---