Unlink data from worksheet in Chart (Excel 97)

Topic

New Reply

I’ve made a template with buttons (and macros). By pushing one of the buttons a calculation is done and a chart is generated. Now I want the user to be able to save the worksheet without macros attached (as the calculation is only needed once). The problem is that I don’t know how to do this. I can’t find a ‘SaveAs’ without macros option. Therefore, I entered a button with a macro to copy the contents of the spreadsheet to another workbook and leave it to the user to save the new workbook. Now I have 2 other problems:
1) in some cases the chart is not copied with the rest (what I do is just copying the whole sheet and pasting it). This seems to be very random: sometimes the chart is copied, sometimes not.
2) how do I unlink the reference to the ‘old’ worksheet in the data series of the chart? As I copied the whole sheet, including the data used for the chart, the name of the sheet in the data series can be the new sheet.

Reply | Quote

Replies

You can copy a whole sheet (as opposed to copying the contents of a sheet) using code like

ActiveWorkbook.Sheets(“Sheet2”).Copy After:=Workbooks(“Workbook2”).Sheets(“Sheet1”)

Doing it this way, all references *within* the sheet should migrate correctly. References to other sheets will still refer to the old workbook, I suppose, but I haven’t tested that.

(of course, you can make this more elegant by using variables for the workbooks and sheets, but this
will give you the basic idea)

Reply | Quote

Thanks for your reply, but this doesn’t work here.

Private Sub CmdCopy_Click()
   Dim sNameWb1 As String
   Dim sNameWb2 As String
   Dim sNameWs1 As String
   Dim sNameWs2 As String
   'original workbook and worksheet
   sNameWb1 = ActiveWorkbook.Name
   sNameWs1 = ActiveSheet.Name
   Workbooks.Add
   'new workbook and worksheet
   sNameWb2 = ActiveWorkbook.Name
   sNameWs2 = ActiveSheet.Name
   Workbooks(sNameWb1).Sheets(sNameWs1).Range("A1:N65").Copy  _
   After:=Workbooks(sNameWb2).Sheets(sNameWs2)
End Sub

I always get an object-defined or application-defined error ‘run-time error 1004’ when the copy statement is executed. Using the de######, I checked the names of the workbooks and sheets and everything seems ok, but copying does not work. If I replace After by Destination and add .Range(“A1”) then the sheet is copied but the data series in the chart are still pointing to the original workbook.

Reply | Quote

Hans,

The attached code will copy only the worksheets to a new workbook. However any code that is in any of the worksheets will be copied as part of the worksheet . So this is only suitable for reomiving code stored in a general module. There is also code to remove the button that invokes the code, and you should change the name of this from

Reply | Quote

Thanks Andrew.
However, this will still make appear the “this workbook contains macros” dialog window when opening the file, as the code in the worksheet module is still there. What I hoped to do is just save the file without any code. But this does not seem possible in an easy way. As the buttons are outside the A1:N65 range, I thought to just copy this range and paste it into another (empty) workbook. Seemed to me as the most easy way to go. But then I want to get rid of the link to the original workbook in my copied chart.
Maybe your approach is the way to go. Removing worksheet code requires code. How to get rid of that?

Reply | Quote

Hans,

Try

Sub DelSheetCode()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
    .DeleteLines 1, .CountOfLines
End With
End Sub

changing “Sheet1” to the name of the sheet in question.

Andrew

Reply | Quote

Hi Andrew,

I think my method will be simpler: Create all on a fresh worksheet (thus without code and links) in the first place.

Reply | Quote

Jan Karel,

The problem is that the template has a nice layout, contains logos, the user enters data in the spreadsheet before the calculation etc. The problem is that I didn’t thought of the allergy of the user against the presence of code in the workbook. And in a certain way he is right; after entering the data and doing the calculation, storing the output would be all that is required. Part of the output is the layout and the data entered by the user, part of it are the calculation results. That’s why getting rid of the code when saving the worksheet would be the best way to go.

I think I’ll try Andrew’s solution. It gives me a chance to learn about the VBE. Thanks anyway.

Reply | Quote

Hans,

I should have made clear to use the CODE name of sheet in place of Sheet1, as that may be different from the actual sheet name as shown on the sheet tab.

If all your code is in the sheet module, you would need to include the VBE code as well so that it can delete itself.

something like the following at th eend of your code :

With ThisWorkbook.VBProject.VBComponents(Me.CodeName).CodeModule
.DeleteLines 1, .CountOfLines
End With

Andrew

Reply | Quote

Hans,

I forgot to include this link to Chip Pearson’s site , which might help if you want to learn about the VBE and coding for it.

Andrew

Reply | Quote

One of my other questions was how to unlink the data from the original worksheet. After some experimenting I came to this (assuming I have a chart named HPChart in the activeworksheet and 1 data series):

Sub test()
    ActiveSheet.ChartObjects("HPChart").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Select
    Application.SendKeys "{F2}"
    Application.SendKeys "{F9}"
    Application.SendKeys "{ENTER}"
End Sub

You’ll get into trouble with this if in case of too many data, but for small data sets it works fine. F2 selects the command line, F9 unlinks the data from its range address, using the actual values as such, and enter is needed to make the whole command effective.

Just wanted to share this with you. Thanks to all!

Reply | Quote

Hi Hans,

Why not:

– open an empty workbook
– do your calcs, but store the results in the new empty wkbk (pastespecial-values?)
– create the chart entirely in the new wkbk.

Reply | Quote