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.
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:
' Program: RangeToPPTLink
' Description: Copies selected range as a picture, pastes into Powerpoint as linked picture
' Called by: Toolbar button "Rng Link"
' Date Programmer Change
' Jon Peltier Original
' 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"
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
' Paste the range
' 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
old, slow, and confused
but at least I'm inconsistent!
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.