Loop through Excel sheets and copy range to Powerpoint, Almost there!!

[Jschroeder71]

Hi Everyone, I've done as much of this as I can on my own (and with help from people much smarter than I). I'm a VBA Newbie.

I have an Excel spreadsheet that creates a PowerPoint Presentation. By clicking a button on each sheet, it copies a range or chart to the Presentation (thats currently open) as a picture and adds a title to the slide. This works but...

What I'm trying to do is get it down to just clicking one button and it loops through each sheet that is not hidden, copies the chart and the title and pastes it to a new Slide in PowerPoint without having to go to each sheet and click a button. Kind of like a "Generate PowerPoint" button.

I have gotten it to the point where it loops through and titles each page, but it wont copy the ranges/charts and it places them in backwards, 5,4,3,2,1 instead of 1,2,3,4,5. I'm not sure where the problem lies.

Any help would be great!

Code is below.

Please Login or Register  to view this content.

Thanks,
John

[mikeTRON]

I would likely wrap a named range around each of the pieces of data you want to bring over to PPT so you can call them out in order in the VBA.

[Jschroeder71]

Thanks for your suggestion, but I'm not sure I understand. Could you elaborate a little bit?

There are currently about 60 sheets in this workbook. They will not all be used each time a presentation is generated. About half will be used at any given time depending on the needs. Right now it looks for sheets that are not hidden and uses the numbered range (which is the same for each page and obviously doesn't work). If I name the ranges, they would have to be a different name on each worksheet. How would I tell it to use the proper named range on the proper sheet? Or am I totally not understanding, Keeping in mind I'm relatively new to VBA, just started to play with it in February.

Thanks,
John

[mikeTRON]

Well the real question is do you WANT the flexibility of looping through ALL sheets, or just the relevant sheets. If you use a named range and explicitly call out the sheet to grab data from you can avoid the problem in which you are having it pull in 5,4,3,2,1.

I think the last time I did this I created a MASTER Excel file that I linked myself to a MASTER Powerpoint. Once they were linked when I wanted to update and create a PDF I would use VBA to update the raw data in the master and then refresh the powerpoint finally saving the PPT as a new filename after breaking the links. I found that easier than trying to copy and paste values or pictures or whatever from Excel to Powerpoint.

If I remember correctly I just made a list of sheets and charts to be looped through and then I could manage the VBA by simply updating my table to loop through. It SOUNDS complicated but it allowed me to hand the process off to someone with ZERO vba experience and have them NOT call me every 5 minutes with a change request.

[Jschroeder71]

I like the idea of having the list to loop through however my VBA knowledge is limited and I'm not sure how to go about doing that.

Just like you, there will be people using this spreadsheet and I don't want them calling every 5 minutes either. This spreadsheet will also be used by another office in another state.

[akhileshgs]

hmm....

I was using this code for that. But it doesnt go to each tab and copy paste. Just from a single sheet

Sub CreatePowerPoint()



 'Add a reference to the Microsoft PowerPoint Library by:
    '1. Go to Tools in the VBA menu
    '2. Click on Reference
    '3. Scroll down to Microsoft PowerPoint X.0 Object Library, check the box, and press Okay
 
    'First we declare the variables we will be using
        Dim newPowerPoint As PowerPoint.Application
        Dim activeSlide As PowerPoint.Slide
        Dim cht As Excel.ChartObject
     Dim PPT As PowerPoint.Application
 Set PPT = New PowerPoint.Application
 PPT.Visible = True
 PPT.Presentations.Open Filename:="\\C\Template.pptx" 'change your ppt path here
     'Look for existing instance
        On Error Resume Next
        Set newPowerPoint = GetObject(, "PowerPoint.Application")
        On Error GoTo 0
     
    'Let's create a new PowerPoint
        If newPowerPoint Is Nothing Then
            Set newPowerPoint = New PowerPoint.Application
        End If
    'Make a presentation in PowerPoint
        If newPowerPoint.Presentations.Count = 0 Then
            newPowerPoint.Presentations.Add
        End If
     
    'Show the PowerPoint
        newPowerPoint.Visible = True
    
    'Loop through each chart in the Excel worksheet and paste them into the PowerPoint
        For Each cht In ActiveSheet.ChartObjects
        
        'Add a new slide where we will paste the chart
            newPowerPoint.ActivePresentation.Slides.Add newPowerPoint.ActivePresentation.Slides.Count + 1, ppLayoutText
            newPowerPoint.ActiveWindow.View.GotoSlide newPowerPoint.ActivePresentation.Slides.Count
            Set activeSlide = newPowerPoint.ActivePresentation.Slides(newPowerPoint.ActivePresentation.Slides.Count)
                
        'Copy the chart and paste it into the PowerPoint as a Metafile Picture
            cht.Select
            ActiveChart.ChartArea.Copy
            activeSlide.Shapes.PasteSpecial(DataType:=ppPasteMetafilePicture).Select
    
        'Set the title of the slide the same as the title of the chart
            activeSlide.Shapes(1).TextFrame.TextRange.Text = cht.Chart.ChartTitle.Text
            
        'Adjust the positioning of the Chart on Powerpoint Slide
            newPowerPoint.ActiveWindow.Selection.ShapeRange.Left = 15
            newPowerPoint.ActiveWindow.Selection.ShapeRange.Top = 80
        
            activeSlide.Shapes(2).Width = 200
            activeSlide.Shapes(2).Left = 505
            
        Next
     
    AppActivate ("Microsoft PowerPoint")
    Set activeSlide = Nothing
    Set newPowerPoint = Nothing
     
End Sub  

[Jschroeder71]

I have managed to figure out its is in fact copying the range to the clipboard properly, however it is not pasting it. I'm not sure why.