Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Distribute Excel Graphs without the underlying data.

    In a workbook with graphs, when I select all cells and copy and paste as values, the data in the graphs are still referencing data in the external workbook. The graphs are to be distributed monthly in Excel and PDF. How do I change the graphs on the distribution copy so that they are not dependent on the workbook with the source data? Excel version is 2003.
    Thanks.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. I have found the easiest way to do that is to use VBA code and put it into Powerpoint as a linked picture. Then you can break the links in PPT, and re-paste into Excel. This reduces the file size considerably. My reason is that here, everything has to be presented in PPT. Note that this copies the range. What I do is select the cells under and around the graph and include that. Actually I put the entire graph in one cell, and use the cell above for title formatting, and the cell below for legend, etc. This allows much greater flexibility for formatting and colors. Of course, you will have to expand the size of the cell, normally I use 60 wide and 150 high. Then when resizing the graph, hold down the OPTION key which forces it to snap to the cell's edge. Also, when selecting the chart, to Format, go to the tab for font and uncheck the box in lower left that says "Auto Scale", and set the font size to whatever you want (8 pt works best). Then I also use no fill and no borders on the graph.

    If you want I can post a small sample to look at.

    Jon Peltier provided the initial code:

    Code:
    '=======================================
    ' Program: RangeToPPTLink
    ' Description: Copies selected range as a picture, pastes into Powerpoint as linked picture
    ' Called by: Toolbar button "Rng Link"
    ' Changes-----------------------------------------------------------
    ' Date         Programmer     Change
    '                   Jon Peltier           Original
    '=======================================
    
    Sub RangeToPPTLink()
    ' Set a VBE reference to Microsoft PowerPoint Object Library for Office,
    ' Adapted from http://peltiertech.com/Excel/XL_PPT.html
    
    Dim PPApp As PowerPoint.Application
    
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    
    ' Make sure a range is selected
    If Not TypeName(Selection) = "Range" Then
        MsgBox "Please select a worksheet range and try again.", vbExclamation, _
            "No Range Selected"
    Else
        Set PPApp = GetObject(, "Powerpoint.Application")
        ' Reference active presentation
        Set PPPres = PPApp.ActivePresentation
        PPApp.ActiveWindow.ViewType = ppViewSlide
        ' Reference active slide
        Set PPSlide = PPPres.Slides(PPApp.ActiveWindow.Selection.SlideRange.SlideIndex)
        
        ' Copy the range as a picture
        Selection.Copy
    
        ' Paste the range
        PPSlide.Shapes.PasteSpecial(Link:=True).Select
        
        ' Align the pasted range
    '    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
    '    PPApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True
    
        ' Clean up
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing
    End If
    
    End Sub
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I checked the VBE reference to MS PowerPoint 11.0 Object Library. Others already checked are Visual Basic for Applications, Microsoft Excel 11.0 Object Library, OLE Automation, and Microsoft Office 11.0 Object Library. I selected a range and ran the code which halted at the line of code "Set PPApp = GetObject(, PowerPoint.Application"). The error message was "ActiveX component can't create object". I closed Excel and tried again with the same results. My PowerPoint executable is spelled "POWERPNT.EXE", but changing the spelling in the code to "PowerPnt" made no difference.
    You may not be able to advise me on this, but I am hoping this is an easy fix.
    Thanks.
    Jerry

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Hmmm. I am using Excel 2003 and it works fine for me. I wonder if it needs the Scripting Runtime to be referenced as well (I have it checked in my References).

    Did you open Powerpoint prior to running the code? The way it is written, Powerpoint needs to be open to a slide.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Thumbs up

    The Excel VBA code worked with PowerPoint open. Thanks.
    Problem solved.
    Jerry

  6. #6
    Join Date
    Oct 2003
    Posts
    1,091
    Great. There is code available somewhere that opens PPT. But I found that I am working on the project in both Excel and PPT, so it will always be open.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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