Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel macro to ppt

Status
Not open for further replies.
avnith

avnith

ISP
Apr 22, 2010
10
US
I am currently working on a requirement where the excel shoud automate powerpoint..

For eg
Jan Feb Mar
East 10 30 40
West 13 14 45
South 34 35 33

Each row in the excel should generate a seperate slide.
So as per the data we r supposed to have 3 slides..

I was able to code in such a way it works for a single slide and get updated automatically when the months grows
But i am not able to get for more than one slide.Please can you give me a clue to continue..
I am really struck with this

I really thank you for your time.
Keep up with your Good work.
Thanks

My code is pastes below written in MS xl 2003
Sub Chart2PPT()
Dim objPPT As Object
Dim objPrs As Object
Dim objGraph As Object
Dim objDataSheet As Object
Dim rngData As Range
Dim intRow As Integer
Dim intCol As Integer

' excel chart data
Set rngData = Range("A1:J2")
' open powerpoint
Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
' existing powerpoint pres
objPPT.Presentations.Open "C:\PPt\Call_volume.ppt"
' chart on slide 2
Set objPrs = objPPT.Presentations(1).slides(2)
' pointer to graph
Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application
' pointer to graphs data sheet
Set objDataSheet = objGraph.Datasheet
' transfer data
For intRow = 1 To rngData.Rows.Count
For intCol = 1 To rngData.Columns.Count
objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
Next
Next
' update to keep changes
objGraph.Update
objGraph.Quit
objPPT.Presentations(1).Save


' tidy up objects
Set rngData = Nothing
Set objGraph = Nothing
Set objDataSheet = Nothing
Set objPrs = Nothing
Set objPPT = Nothing
End Sub
 
Sort by date Sort by votes


Hi,

You almost had it.

Moved several things around and referenced each slide with the row counter...
Code: 
     ' excel chart data
    Set rngData = Range("A1:J2")
     ' open powerpoint
    Set objPPT = CreateObject("Powerpoint.application")
    objPPT.Visible = True
     ' existing powerpoint pres
    objPPT.Presentations.Open "C:\PPt\Call_volume.ppt"
     
    ' transfer data
    For intRow = 1 To rngData.Rows.Count
        For intCol = 1 To rngData.Columns.Count
            objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
        Next
        
        ' chart on slide
        Set objPrs = objPPT.Presentations(1).slides(intRow)
         ' pointer to graph
        Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application
         ' pointer to graphs data  sheet
        Set objDataSheet = objGraph.Datasheet
        
         ' update to keep  changes
        objGraph.Update
        objGraph.Quit
        objPPT.Presentations(1).Save
    Next
    
     
     ' tidy up  objects

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
SkipVought,
Thanks for your reply..
I am new to excel VBA..
I am still getting error message

Run time error '91'

Object variable or with block variable not set..

How can the datasheet in the chart get updated automatically with excel range as the months grows..
Please advice..
 
Upvote 0 Downvote



error on what statement? Use your DEBUG button

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
It showing me a error in this line pasted below

objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
 
Upvote 0 Downvote


run your proc to the error.

DEBUG

highlight each, in turn and Debug > Add watch
[tt][highlight]
objDataSheet.Cells(intRow, intCol)
[/highlight][highlight]
rngData.Cells(intRow, intCol)
[/highlight][/tt]
Report back what you observe in the Watches Window

faq707-4594

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote

This was the message i got when i performed Add Watch..

Watch : powerpoint : <Expression not defined in context> : Empty : Module1.Chart2PPT
 
Upvote 0 Downvote
I think that Skip meant this:
Code: 
' excel chart data
Set rngData = Range("A1:J2")
' open powerpoint
Set objPPT = CreateObject("Powerpoint.application")
objPPT.Visible = True
' existing powerpoint pres
objPPT.Presentations.Open "C:\PPt\Call_volume.ppt"
' transfer data
For intRow = 1 To rngData.Rows.Count
  ' chart on slide
  Set objPrs = objPPT.Presentations(1).slides(intRow)
  ' pointer to graph
  Set objGraph = objPrs.Shapes(2).OLEFormat.Object.Application
  ' pointer to graphs data  sheet
  Set objDataSheet = objGraph.Datasheet
  For intCol = 1 To rngData.Columns.Count
    objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol)
  Next
  ' update to keep  changes
  objGraph.Update
  objGraph.Quit
  objPPT.Presentations(1).Save
Next
' tidy up  objects

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
I tried the code you pasted
its giving me an eror message

OLEFormat.Object:Invalid Request.This property only applies to OLE Objects

Set objGraph = objPrs.Shape(2).OLEFormat.Object.Application

When I performed a Add watch
Watch : : powerpoint : <Expression not defined in context> : Empty : Module1.Chart2PPT
 
Upvote 0 Downvote



<Expression not defined in context>

indicates that you did not SELECT the entire OBJECT EXPRESSION.

Highlight ONLY the part of the expression FIRST, that is to the LEFT of the [EQUAL] sign and perform the Add Watch.

Then highlight ONLY the part of the expression, that is to the RIGHT of the [EQUAL] sign and perform the Add Watch.

You should have TWO Watch Expressions in the Watch Window.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
When I highlight the code as you told me,
Its giving me out of context error message
 
Upvote 0 Downvote


I added PHV's code and got this far with just a test ppt and ONE SHAPE, being the MS GRAPH...
Code: 
    Dim rngData, objPPT, objPrs, objGraph, objDataSheet, intRow, intCol
    ' excel chart data
    Set rngData = Range("A1:J2")
     ' open powerpoint
    Set objPPT = CreateObject("Powerpoint.application")
    objPPT.Visible = True
     ' existing powerpoint pres
    objPPT.Presentations.Open "C:\test.ppt"
     
    ' transfer data
    For intRow = 1 To rngData.Rows.Count    '[b]
        Set objPrs = objPPT.Presentations(1).slides(intRow)
        ' pointer to graph
        Set objGraph = objPrs.Shapes(1).OLEFormat.Object.Application
        ' pointer to graphs data  sheet
        Set objDataSheet = objGraph.Datasheet
        '[/b]
        For intCol = 1 To rngData.Columns.Count '[highlight]
            objDataSheet.Cells(intRow, intCol) = rngData.Cells(intRow, intCol) ''[/highlight]
        Next
'.....
and the [highlight]HIGHLIGHTED[/highlight] statment executed.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Sir,

Thanks for your response..

Run-time error

OleFormat.object:Invalid request.This property only applies to ole objects.

When I add watches
objPrs.Shapes(1).OLEFormat.Object.Application <Oleformat.object:invalid request.This property only apples to OLE objects>
 
Upvote 0 Downvote


Do you have ONE object on your slide that is an MS GRAPH object?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


and your MS Graph chart is the ONLY object on the slide? No text boxes or rectangles or ANYTHING, so that when you do Edit > Select ALL you can see ONLY the MS Graph Object selected?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
I remove the textboxes and tried running it again..
Run-time error

slide.shapes:Object does not exist


Set objGraph = objPrs.Shape(2).OLEFormat.Object.Application
 
Upvote 0 Downvote



Let me tell you that it DOES run. I have run it, with the caveat as stated: ONE MS GRAPH Object and NOTHING else on that slide.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote


BTW,

It seems you have a SLIDE for each ROW in your source data.

EACH SLIDE must adhere to the conditions as stated.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Question
CTRL + BREAK from second macro button
Replies
5
Views
650
strongm
strongm
remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
954
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
feipezi
  • Locked
  • Question
How to make a recorded Excel macro (convert a text file to a Workbook) more efficient?
Replies
11
Views
530
SkipVought
S
remeng
  • Locked
  • Question
Excel VBA to Pass Var between file macros after var changes
Replies
13
Views
566
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top