Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unlink data from worksheet in Chart (Excel 97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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)

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

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

    <pre>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
    </pre>


    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.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    Hans,

    Try <pre>Sub DelSheetCode()
    With ThisWorkbook.VBProject.VBComponents("Sheet1").Code Module
    .DeleteLines 1, .CountOfLines
    End With
    End Sub</pre>

    changing "Sheet1" to the name of the sheet in question.

    Andrew

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    Hi Andrew,

    I think my method will be simpler: Create all on a fresh worksheet (thus without code and links) in the first place.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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.

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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).C odeModule
    .DeleteLines 1, .CountOfLines
    End With

    Andrew

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unlink data from worksheet in Chart (Excel 97)

    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):

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


    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •