Excel spreadsheets with links. Copy/SaveAs and remove links.

[puforee]

I have a spreadsheet with about 26 charts. The charts are driven by links to an Access DB.

I want to make the charts available to others...WITHOUT the links. I don't want them to be able to update their version of the spreadsheet.

Can I save as or copy paste to a new location and keep the data but remove the updatable links?

If I can, how please.

Thanks,

 

[kjv1611]

Offhand, I'd say you may have to do this with VBA.

You could create a sub procedure that looped through copying each sheet, then going to each chart object, and "unlink" it.

I guess to cut down on code, I'd probably just:
1. Copy the entire original workbook
2. Then run code within to loop through sheets, unlinking charts.

Here is one blog post I found on the topic. It is kind of long, but if it's something you do regularly, finding an automated solution will well be worth the effort:

http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html

If you do decide to go down the VBA path, and run into specific problems or questions, you'll get the best help in the VBA forum:
forum707

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

I'd COPY each Chart and Paste Special -- PICTURE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[puforee]

Thank you both. I was hoping for a simpler solution but it is what it is. I will look up the references.

Thanks,

 

[kjv1611]

If you're doing this ONE time, then copying and pasting special as Skip suggested will take the least time and effort. You'll have to do it 26 times, but it'll still take less time than coding a solution. However, if it's something you do regularly, then coding it will be very beneficial.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Record a macro of copying and pasting one chart.

Copy the recorded code and then paste your recorded code into a post for help customizing to copy/paste all your charts.

Where are your 26 charts: all in one sheet, on multiple sheets?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[puforee]

Sorry I am late getting back to everyone. First, I don't want to copy paste special as a picture. There are controls (macro driven) to allow the users to move from one chart/data to another. I want to leave the macros in tact. To answer Skips question...I made a math error above. I actually have 52 charts, each in its own tab in the workbook. I have two categories of data. The first 2 charts show a stacked column chart summarizing each category by month. I track 13 months. Then each category (using controls) can go to each months individual charts. These break out the data more finitely. And finally, from the use can select to look at the actual data. This allows them to see the chart data plus comments that have been entered by the data. All this is controlled by Button Controls with macros attached.

So, macros and Control Command Buttons must stay to allow easy movement between charts. The charts could be pictures but that is really manually intensive since I have to do it every couple of weeks. I will now look at kjv1611's link to see just what I am in for.

Another Idea: Maybe the user workbook can be updateable but only with a password. Any ideas on this approach?

 

[kjv1611]

Hmm... if your buttons/macros are only used to swap between sheets, then that should not be affected by the copy/paste of the charts.. just have to get perhaps a little more creative... for instance...

[ol 1][li]Copy whole workbook[/li]
[li]Within each worksheet, copy/paste special as image[/li]
[li]Within each worksheet, AFTER copying to image, delete original chart.[/li]
[li]Your code/buttons remain intact[/li][/ol]

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[puforee]

Any thoughts on code to only allow updates if a password is entered first?
Thanks for the feed back. I am keeping all comments until the best solution appears.

 

[kjv1611]

Sure - that's a different topic, but basically, in your code, you want a conditional statement: probably an IF statement:

' First use an Inputbox to ask for password
sInputPassword = Inputbox("Please enter password")

' Next check that against your global/public password variable:
If sPasswordVariable = sInputPassword Then
     ' Proceed with whatever you want to do, such as copying or modifying sheets
Else
     ' You could either not include an Else or include something like 
     MsgBox "Incorrect Password - changes not allowed"
End If

That's very rough untested code there... just a quick stab at what you're talking about.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[combo]

Sticking to VBA, you can create a macro that breaks links. You will stay with charts with values in chart series. The macro can be modified to delete links from access via automation, if you need so:

Dim wb As Workbook
Dim lnks As Variant, lnk As Variant
Set wb = ActiveWorkbook 'or other you need
lnks = wb.LinkSources
On Error Resume Next
For Each lnk In lnks
    wb.BreakLink lnk, xlLinkTypeExcelLinks
Next lnk
Err.Clear
End Sub

If you embed data in your workbook, you may need to delete Connections too.


combo