Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to copy chart/ fix file name (xls 97)

    Hello, i am trying to fine tune a part of my database monster. I have an xls file that contains links to 6 other excel files and mixes and matches what i want from each into 1 nice spreadsheet. (Takes various data and moves to one file using links).

    However, since i want to preserve this file and its links i have made a macro that would open up a new worksheet, copy all the data and paste special "as values". This makes a new sheet that the user could modify if they liked and the file with the links that grabs the data stays in tact. That works fine.

    The problem is in copying charts/graphs that are located on a 2nd sheet in the links file. I can copy and paste them from the 'links' to the 'book1' manually and it will work. However, if i try to automate it using a macro i get "unable to get the ChartObjects property of the worlsheet class". Another problem is the macro records the creation of a new worksheet as either book1, book2, book3... etx. depending on how many books i have previously made before closing excel ( ie it continuously counts up). So if the macro is set and book1 was created if the next time i try run the macro and excel is passed book1 i get an error. i need to set the new workbook title to something stable, or let the user pick it before the macro is run?

    Here is what the macro for copying the chart looks like.

    Sub test2()
    '
    ' test2 Macro
    ' Macro recorded 1/4/02 by Edward
    '

    '
    ActiveWindow.Visible = False
    Workbooks.Add
    ActiveWindow.WindowState = xlMinimized
    ActiveSheet.ChartObjects("Chart 1025").Activate
    ActiveWindow.Visible = False
    Windows("Quarterly Risk Summary.xls").Activate
    Range("P3").Select
    ActiveSheet.ChartObjects("Chart 1025").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Copy
    ActiveWindow.Visible = False
    Windows("Book3").Activate
    ActiveWindow.WindowState = xlMaximized
    ActiveSheet.Paste
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveWindow.Visible = False
    Windows("Book3").Activate
    Range("B32").Select
    ActiveWindow.WindowState = xlMinimized
    ActiveSheet.ChartObjects("Chart 1025").Activate
    Windows("Quarterly Risk Summary.xls").SmallScroll Down:=18
    ActiveWindow.Visible = False
    Windows("Quarterly Risk Summary.xls").Activate
    ActiveSheet.ChartObjects("Chart 1036").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartArea.Copy
    ActiveWindow.Visible = False
    Windows("Book1").Activate
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.SmallScroll Down:=6
    Range("A34").Select
    ActiveSheet.Paste
    ActiveSheet.ChartObjects("Chart 2").Activate
    Windows("Book3").SmallScroll Down:=21
    ActiveWindow.Visible = False
    ActiveWindow.WindowState = xlMinimized
    ActiveSheet.ChartObjects("Chart 1036").Activate
    End Sub


    Again a stable file to paste the material, a way to paste the charts as well is needed, thanks.

    Ed

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to copy chart/ fix file name (xls 97)

    Ed, it's hard to fix monsters, but here is several points that should help:
    1) the Add method always returns the object, so grab it because you cannot read minds like the macro recorder. In your case, <pre>Option Explicit
    Sub test2()
    dim wbNew as Workbook
    Set wbNew = Workbooks.Add
    ' get some stuff
    wbNew.Sheets("Sheet1").Paste</pre>


    2) the unable to get ChartObjects is again because the macro recorder knows the name of the chart, but you don't. If there is only one chart, then just reference it as ChartObjects(1); otherwise, use "For Each co in ChartObjects", where co is dim'med as ChartObject

    3) But, you have another major problem, copying the chart creates a link back to your original data. Don't you want to have it link to the values that you have pasted in the new worksheet?

    HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    2 Star Lounger
    Join Date
    Jul 2001
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to copy chart/ fix file name (xls 97)

    We have changed our mission with 'the monster' please check out my new post:

    http://www.wopr.com/cgi-bin/w3t/showflat.p...sb=5&o=0&fpart=

Posting Permissions

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