Excel to Power Point 3

[shadyness]

Hey all,

I have a summary sheet in an Excel 2000 model, which contains three graphs. I would like to program a module that would open up a power point file in the same folder (called export.ppt) and then paste the slides to a particular slide (slide number 4). I have written VBA scripts before that open/print external files using a ShellExecute command, but I don't think this applicable since Power Point is a Microsoft product. Any help or direction would be much appreciated!!

Thanks
<== Some people say they are afraid of heights. With me its table widths. ==>

 

[lambuhere1]

You need to call Power Point application and then export the data from the sheet to the powerpoint slide. Check the references for Microsoft Power Point Object 9.0 Library in Tools ->References in VBA.

Ram P

 

[RobBroekhuis]

Try the following sub on for size (after setting the reference as Ram suggested), and modify to get it to do exactly what you need. The code is circuitous - Powerpoint is picky about what you can do in what view, and I'm not very familiar with PPT VBA, but it does work as is.

Rob

 

[SkipVought]

You are not clear in what you are asking. Is it...

1. You want to take stuff (charts) from Excel and stick then in a brand new Power Point presentation each time or

2. You have these Excel chart(s) that you want to link to one Power Point presentation set of slides and be current every time you open the presentation??? Skip,
Skip@theofficeexperts.com

 

[RobBroekhuis]

Oops - I guess I never actually copied the code in. Here goes:

Sub PasteToPPT()
Dim ppt As PowerPoint.Application
Set ppt = GetObject(, &quot;powerpoint.application&quot
ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen
ppt.ActiveWindow.ViewType = ppViewOutline
ppt.ActivePresentation.Slides(4).Select
ppt.ActiveWindow.ViewType = ppViewSlide
ppt.ActiveWindow.View.Paste
With ppt.ActiveWindow.Selection.ShapeRange
.Height = 300
.Top = 50
.Left = 50
End With
Set ppt = Nothing
End Sub
Rob

 

[shadyness]

Sorry for a late response, but my ISP went down. Actually Skip, either option 1 or 2 would be valid. The model is used for financial analysis (like all excel models I guess) and this Power Point copy will always sit in the same folder as the excel model. I am, writing a script thatr will find the uders desktop, then &quot;save as&quot; the power point file with a time-date stamp. So I guess it could very well be a hybrid of your points 1 and 2. However, I am more inclined to keep the presentation blank, paste in data, then save as and close. Rob, thank you for your input, I was attemtping some similar code after activating the Word PP 9.0 Object Libararies. --Also, I had heard that you need to paste the objects into Word first and then copy and paste them into Power Point, in order to make them paste as pictures. Anyone know if that is true? <== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen

This method copies the Excel chart as a picture. The parameters I specified (&quot;screen&quot; settings) result in a smaller picture size than using &quot;printer&quot; settings. See how it works for you.
Rob

 

[shadyness]

Thanks Rob,

I know that you have assisted me before in the past, and it is greatly appreciated. One other question. As I was debugging this thing I noticed an irregularity. I have the 10 calculations tabs and one summary tab. Whoever started this project used hyperlinks to navigate around the workbook (not something that I usually do, but obviously normal). Now, so that the summary page knows which calculation page to pull data from I made each calculation page have this activation command:

Sheet14.Cells(5, 3) = &quot;X&quot;

Whereas X can be 1 - 10 and the when the summary sheet activates it runs a Sub that looks to see what what value is in that cell. Now if you simply move through the various worksheets using the lower tabs the summary page updates fine. However, if you go to a calculation page using the provided hyperlink (and perform tasks on a given sheet) and then navigate to the summary tab it will not update.

Any ideas why the activation of a worksheet is different from opening it from a hyperlink?

<== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

No - I haven't used hyperlinks very much myself. Are you saying the worksheet_activate event doesn't fire when you use a hyperlink?
Rob

 

[shadyness]

Yeah. For some reason if you choose the tab the activate commands execute properly, but fail (with no error) when navigated to directly using the hyperlink &quot;in this document&quot; option for navigation. <== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

By golly you're right. Luckily, there is another event called Workbook_SheetFollowHyperlink. If you test for entering a sheet in that event as well, you'll have both routes covered.
Rob

 

[shadyness]

I do not have much experience with the Workbook_SheetFollowHyperlink declaration, and things did not exactly work as I would have hoped. It caused some serious errors that did not happen with worksheet_activate. I need to research this topic a little more. If anyone has any tips with Workbook_SheetFollowHyperlink, please let me know!!! <== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

What's the code you're using? I don't think there is anything particularly different about the followhyperlink event. What serious errors did you get?
Rob

 

[shadyness]

This is a sample event:

Private Sub Worksheet_Activate()
[a1].Select
Sheet18.Cells(5, 3) = &quot;1&quot;
End Sub

there are some much more complex ones, but even this statement does not run correctly when placed in a follow hyperlink event. However, if you click on the worksheet via the tab, the value sets to the desired number and evrything is happy in the world. I do not think that I am doing something correctly, thats for certain.
<== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

What happens when you try to use the hyperlink event? The more specific the information you provide, the easier it will be to help.
Rob

 

[lambuhere1]

You can try tweaking the following code to meet your needs.

I use the following code to copy each chart onto each slide on a blank Power Point Presentation. I hope you can make the changes to meet your requirements. Note that the charts in Power Point are NOT linked to the charts in Excel. For that we require to develop new logic and code.

Dim oppt As New PowerPoint.Application
Dim pptpres As Presentation
Dim pptslide As Slide
Dim ch As Chart
Dim wkb As Workbook
Dim strtempwkb As String

'build a temporary workbook

this_path = ActiveWorkbook.Path
strtempwkb = this_path + &quot;\&quot; + &quot;tempwkb.xls&quot;

'create a new presentation
oppt.Visible = msoTrue




Set pptpres = oppt.Presentations.Add
With pptpres.Slides

lheight = pptpres.PageSetup.SlideHeight

lwidth = pptpres.PageSetup.SlideWidth


'place each chart on slide
For Each ch In ActiveWorkbook.Charts

'copy the chart to a new workbook
ch.Copy
Set wkb = ActiveWorkbook

wkb.SaveAs strtempwkb

Set pptslide = .Add(.Count + 1, ppLayoutBlank)
pptslide.Shapes.AddOLEObject Left:=(lwidth ), Top:=(lheight ), Width:=(lwidth ), Height:=(lheight), Filename:=strtempwkb, link:=msoFalse


'close temp workbook and delete it
wkb.Close savechanges:=msoFalse
Kill strtempwkb
Next ch

 

[shadyness]

I managed to program the export of charts to power point no problem. Where and issue arises, is in the copying and pasting of a cell range? Because it was not working I tried opening a blank Word Doc and pasting it into there, then re-copying it and pasting it into Power Point. The code I used for export looked like this:
[red]

Sub ChartToPresentation()
'Dimension the var's
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim msgPrompt As String
Dim strPathName As String
Dim vAnswer As Variant

'the word in between file vars
Dim wrdDoc As Object
Dim wrdApp As Object


'**************STEP ONE: COLLECT PATHNAME****************
'Give the prompt its first value value
 msgPrompt = "You are about to export summary to Power Point."
 MsgBox msgPrompt, vbInformation, "Are you ready?"
    
'Start the input box to get the value
'!!!!! This is not being used right now !!!!!!!!!
 msgPrompt = "Please enter the full drive-path for the Power Point Presentation"
 vAnswer = Application.InputBox(msgPrompt, "Pathname?", Type:=2)
    If vAnswer = "" Then
        msgPrompt = "You have failed to enter a drive path."
        msgPrompt = msgPrompt + vbCrLf
        msgPrompt = msgPrompt + "Please Try Again."
        MsgBox msgPrompt, vbCritical, "No Drive-Path!"
    Else
    strPathName = vAnswer
    End If
    
'*************STEP TWO: BEGIN EXECUTE *********************
    '+++++++++++CHART ONE +++++++++++++++
    
'grabs the first chart for pasting
 Sheet4.ChartObjects("Chart 2").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen
 ActiveWindow.Visible = False
    

    ' Reference existing instance of PowerPoint 2000
    Set PPApp = GetObject(, "Powerpoint.Application.9")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    
    'This will paste the first chart into place
    PPApp.ActiveWindow.ViewType = ppViewOutline
    PPApp.ActivePresentation.Slides(3).Select
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPApp.ActiveWindow.View.Paste
        With PPApp.ActiveWindow.Selection.ShapeRange
            .Height = 170
            .Top = 100
            .Left = 330
        End With

    '+++++++++++CHART TWO+++++++++++++++
'grabs the Second chart for pasting
 Sheet4.ChartObjects("Chart 7").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen
 ActiveWindow.Visible = False
    

    ' Reference existing instance of PowerPoint 2000
    Set PPApp = GetObject(, "Powerpoint.Application.9")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    
    'This will paste the first chart into place
    PPApp.ActiveWindow.ViewType = ppViewOutline
    PPApp.ActivePresentation.Slides(3).Select
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPApp.ActiveWindow.View.Paste
        With PPApp.ActiveWindow.Selection.ShapeRange
            .Height = 190
            .Top = 270
            .Left = 330
        End With
        
      '+++++++++++CHART THREE+++++++++++++++
'grabs the third chart for pasting
 Sheet4.ChartObjects("Chart 6").Activate
 ActiveChart.ChartArea.Select
 ActiveChart.CopyPicture xlScreen, xlPicture, xlScreen
 ActiveWindow.Visible = False
    

    ' Reference existing instance of PowerPoint 2000
    Set PPApp = GetObject(, "Powerpoint.Application.9")
    ' Reference active presentation
    Set PPPres = PPApp.ActivePresentation
    
    'This will paste the first chart into place
    PPApp.ActiveWindow.ViewType = ppViewOutline
    PPApp.ActivePresentation.Slides(3).Select
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPApp.ActiveWindow.View.Paste
        With PPApp.ActiveWindow.Selection.ShapeRange
            .Height = 190
            .Top = 270
            .Left = 509
        End With
       

'<====== HERE IS WHERE THE PROBLEM STARTS ======>
    '+++++++++++Graph ONE+++++++++++++++
'grabs the executive summary
    Sheet4.Range(&quot;A4:c19&quot;).Activate
    Sheet4.Range(&quot;A4:c19&quot;).CopyPicture xlScreen, xlBitmap
    ActiveWindow.Visible = False
    
 'the word inbetween
 Set wrdApp = CreateObject(&quot;Word.Application&quot;)
 Set wrdDoc = wrdApp.documents.Add()
' Pastes the information on the Clipboard into the Word document as
' an Enhanced Metafile.
  With wrdApp.ActiveWindow.Selection
       .Paste
       .Select
       .Copy  ' Copy the MetaFile back to the clipboard
  End With
  Word.ActiveWindow.Selection.s
'This will paste the first chart into place
    PPApp.ActiveWindow.ViewType = ppViewOutline
    PPApp.ActivePresentation.Slides(3).Select
    PPApp.ActiveWindow.ViewType = ppViewSlide
    PPApp.ActiveWindow.View.Paste
        With PPApp.ActiveWindow.Selection.ShapeRange
            .Height = 250
            .Top = 100
            .Left = 30
        End With
 ' Close the document without saving changes.
  wrdDoc.Close (wdDoNotSaveChanges)
  wrdApp.Quit
       
 ' Clean up
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
    Set wrdApp = Nothing
    Set wrdDoc = Nothing

[/red]

Basically what happens is, everything pastes into the Power Point Slide alright, but Excel just goes blank and you can not see or reveal any of the worksheets. If you close and save the file it will remain in this &quot;stuck&quot; state unless you open the file with Internet Explorer (and I think that is only because it will not run the macros).

So basically I have a model that gets stuck on automated export and the Worksheet_Activate() events do not work in Win2000P if you open a worksheet via hyperlink from another worksheet in the same workbook (Everything seems to work fine in WinXP). At this point I am totally lost, so if anyone knows anything about either problem please let me know.

<== Some people say they are afraid of heights. With me its table widths. ==>

 

[RobBroekhuis]

What was the problem with pasting directly into Powerpoint instead of via Word? I think maybe working with two applications at once gets too much for poor Excel...
Rob

 

[shadyness]

No, the same thing happened when you removed the Word in-between step. And if you drop out the copying and pasting of the cell range then it works fine. With just the charts. <== Some people say they are afraid of heights. With me its table widths. ==>

 

[lambuhere1]

You can use

Set copy_range = Sheet4.Range(&quot;A4:c19&quot

and use it in your Power Point Paste Mode.

I dont think you need WORD Interface... It takes more of your computer resources.