Software·43,836 discussions

VBA Macro to transfer data fro excel to Powerpoint

Locked

Hi,
I need to present a chart with the data in Excel dynamically using VBA Macro

Topic

Clarifications

In reply to VBA Macro to transfer data fro excel to Powerpoint

Clarifications

me to

In reply to VBA Macro to transfer data fro excel to Powerpoint

did you ever figure out how?

Simple Solution

In reply to VBA Macro to transfer data fro excel to Powerpoint

For a quick and simple solution check out the tool at: http://software.techrepublic.com.com/download.aspx?docid=231672

Run an excel macro from within PowerPoint

In reply to VBA Macro to transfer data fro excel to Powerpoint

This is how I do it:

Sub XLTest()
Dim XL as Object

Set XL = CreateObject “Excel.Application”)

XL.Workbooks.Open “C:\My Documents\ExcelFile.xls”

‘ If there is more than one macro called TestMacro,
‘ the module name would be required as in
‘
‘ XL.Run “Module1.TestMacro”
‘
‘ to differentiate which routine is being called.
‘
XL.Run “LoopShts”

End Sub

*********
Sub Copy_Paste_to_PowerPoint()

‘Requires a reference to the Microsoft PowerPoint Library via the Tools – Reference menu in the VBE
Dim ppApp As PowerPoint.Application
Dim ppSlide As PowerPoint.Slide

‘Original code sourced from Jon Peltier http://peltiertech.com/Excel/XL_PPT.html
‘This code developed at http://oldlook.experts-exchange.com:8080/Applications/MS_Office/Excel/Q_21337053.html

Dim SheetName As String
Dim TestRange As Range
Dim TestSheet As Worksheet
Dim TestChart As ChartObject

Dim PasteChart As Boolean
Dim PasteChartLink As Boolean
Dim ChartNumber As Long

Dim PasteRange As Boolean
Dim RangePasteType As String
Dim RangeName As String
Dim AddSlidesToEnd As Boolean
Dim shts As Worksheet
‘Parameters

‘SheetName – name of sheet in Excel that contains the range or chart to copy

‘PasteChart -If True then routine will copy and paste a chart
‘PasteChartLink -If True then Routine will paste chart with Link; if = False then paste chart no link
‘ChartNumber -Chart Object Number
‘
‘PasteRange – If True then Routine will copy and Paste a range
‘RangePasteType – Paste as Picture linked or unlinked, “HTML” or “Picture”. See routine below for exact values
‘RangeName – Address or name of range to copy; “B3:G9” “MyRange”
‘AddSlidesToEnd – If True then appednd slides to end of presentation and paste. If False then paste on current slide.

‘use active sheet. This can be a direct sheet name

SheetName = ActiveSheet.Name

‘Setting PasteRange to True means that Chart Option will not be used
PasteRange = True
RangeName = (“A3:e5″) ‘”MyRange”
RangePasteType = “Picture”
RangeLink = True

PasteChart = False
PasteChartLink = True
ChartNumber = 1

AddSlidesToEnd = True

‘Error testing
On Error Resume Next
Set TestSheet = Sheets(SheetName)
Set TestRange = Sheets(SheetName).Range(RangeName)
Set TestChart = Sheets(SheetName).ChartObjects(ChartNumber)
On Error GoTo 0

If TestSheet Is Nothing Then
MsgBox “Sheet ” & SheetName & ” does not exist. Macro will exit”, vbCritical
Exit Sub
End If

If PasteRange And TestRange Is Nothing Then
MsgBox “Range ” & RangeName & ” does not exist. Macro will exit”, vbCritical
Exit Sub
End If

If PasteRange = False And PasteChart And TestChart Is Nothing Then
MsgBox “Chart ” & ChartNumber & ” does not exist. Macro will exit”, vbCritical
Exit Sub
End If

‘Look for existing instance
On Error Resume Next
Set ppApp = GetObject(, “PowerPoint.Application”)
On Error GoTo 0

‘Create new instance if no instance exists
If ppApp Is Nothing Then Set ppApp = New PowerPoint.Application
‘Add a presentation if none exists
If ppApp.Presentations.Count = 0 Then ppApp.Presentations.Add

‘Make the instance visible
ppApp.Visible = True

‘Check that a slide exits, if it doesn’t add 1 slide. Else use the last slide for the paste operation
If ppApp.ActivePresentation.Slides.Count = 0 Then
Set ppSlide = ppApp.ActivePresentation.Slides.Add(1, ppLayoutBlank)
Else
If AddSlidesToEnd Then
‘Appends slides to end of presentation and makes last slide active
ppApp.ActivePresentation.Slides.Add ppApp.ActivePresentation.Slides.Count + 1, ppLayoutBlank
ppApp.ActiveWindow.View.GotoSlide ppApp.ActivePresentation.Slides.Count
Set ppSlide = ppApp.ActivePresentation.Slides(ppApp.ActivePresentation.Slides.Count)
Else
‘Sets current slide to active slide
Set ppSlide = ppApp.ActiveWindow.View.Slide
End If
End If

‘Options for Copy & Paste Ranges and Charts
If PasteRange = True Then
‘Options for Copy & Paste Ranges
If RangePasteType = “Picture” Then
‘Paste Range as Picture
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteDefault, link:=RangeLink).Select
Else
‘Paste Range as HTML
Worksheets(SheetName).Range(RangeName).Copy
ppSlide.Shapes.PasteSpecial(ppPasteHTML, link:=RangeLink).Select
End If
Else
‘Options for Copy and Paste Charts
Worksheets(SheetName).Activate
ActiveSheet.ChartObjects(ChartNumber).Select
If PasteChartLink = True Then
‘Copy & Paste Chart Linked
ActiveChart.ChartArea.Copy
ppSlide.Shapes.PasteSpecial(link:=True).Select
Else
‘Copy & Paste Chart Not Linked
ActiveChart.CopyPicture Appearance:=xlScreen, Size:=xlScreen, Format:=xlPicture
ppSlide.Shapes.Paste.Select
End If
End If

‘Center pasted object in the slide
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True
ppApp.ActiveWindow.Selection.ShapeRange.Align msoAlignMiddles, True

AppActivate (“Microsoft PowerPoint”)
Set ppSlide = Nothing
Set ppApp = Nothing

End Sub

**************

Sub loopShts()

Dim shts As Worksheet

For Each shts In ActiveWorkbook.Worksheets
If shts.Name = “Summary” Then
GoTo NoSummary
Else
If shts.Name Like “*” & “Summary” Then
shts.Select
Call Copy_Paste_to_PowerPoint
Else
GoTo NoSummary
End If
End If
NoSummary:
Next shts

End Sub

Help

In reply to Run an excel macro from within PowerPoint

I am trying to follow what is in post 4, but I am getting stuck at XL.Run “LoopShts”, I keep getting an error saying it cannot run the macro “LoopShts”, but I do have the sub in the same module 1. I am using 2007 if that makes a difference..

Excel macro to ppt

In reply to Run an excel macro from within PowerPoint

I am in need of your help.

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..
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

Why not use EzPaste?

In reply to Excel macro to ppt

Hello,

You should maybe consider using EzPaste, a tool that completely automates the transfer from Excel to PowerPoint and more (www.EzPaste.net)

ever heard of a do loop?

In reply to Excel macro to ppt

x=1
Do while x<4
'stuff here ex: slides(x)
x=x+1
Loop

help required

In reply to VBA Macro to transfer data fro excel to Powerpoint

i need a macro which can copy any form of data on excel into power point additionally there should be a button on excel sheet by pressing which the macro runs
please if some body can help me as soon as possible

[Author]

Replies