Excel to powerpoint presentation – VBA

MAC USER
Office for mac 2011

Hello, 
I am trying to create an excel spreadsheet to design the reports of the company I am working in. The goal is to create everything (tables, graphs, texts) on Excel and then create the powerpoint by using VBA. 
At this point, I succeeded in writing a VBA code to create the powerpoint and “transfer” what I need. But the range selected in the excel spreadsheet is paste as a picture in the powerpoint and I would like to paste each objects individually so that the powerpoint can be “dynamic”.
Is it possible to have a code that says:(and not one by one)
“select all objects” -> picture, shapes, tables, graphs, SmartArt
“copy all objects”
“Paste all objects” -> in the same display as in the power point. 
Since I am going to create the tables for the graphs in the same sheets, the code is also suppose to say “selects all objects in this specific range” and not in all the spreadsheet. 
Here is my code: 

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin:0cm;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:12.0pt;
font-family:Cambria;
mso-ascii-font-family:Cambria;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Cambria;
mso-hansi-theme-font:minor-latin;}

Sub WorkbooktoPowerPoint()

 

‘Step 1:  Declare your variables

    Dim pp As Object

    Dim PPPres As Object

    Dim PPSlide As Object

    Dim xlwksht As Worksheet

    Dim MyRange As String

    Dim MyTitle As String

 

‘Step 2:  Open PowerPoint, add a new presentation and make visible

    Set pp = CreateObject(“PowerPoint.Application”)

    Set PPPres = pp.Presentations.Add

    pp.Visible = True

 

 

‘Step 3:  Set the ranges for your data and title

    MyRange = “B2:L38”  ‘<<<Change this range

 

‘Step 4:  Start the loop through each worksheet

    For Each xlwksht In ActiveWorkbook.Worksheets

    xlwksht.Select

    Application.Wait (Now + TimeValue(“0:00:1”))

 

‘Step 5:  Copy the range as picture

    xlwksht.Range(MyRange).CopyPicture _

    Appearance:=xlScreen, Format:=xlPicture

 

‘Step 6:  Count slides and add new blank slide as next available slide number

          ‘(the number 12 represents the enumeration for a Blank Slide)

    SlideCount = PPPres.Slides.Count

    Set PPSlide = PPPres.Slides.Add(SlideCount + 1, 12)

    PPSlide.Select

 

‘Step 7:  Paste the picture and adjust its position

    PPSlide.Shapes.Paste.Select

    pp.ActiveWindow.Selection.ShapeRange.Align msoAlignCenters, True

    pp.ActiveWindow.Selection.ShapeRange.Top = 1

    pp.ActiveWindow.Selection.ShapeRange.Left = 1

    pp.ActiveWindow.Selection.ShapeRange.Width = 723

 

 

‘Step 8:  Add the title to the slide then move to next worksheet

    Next xlwksht

 

‘Step 9:  Memory Cleanup

    pp.Activate

    Set PPSlide = Nothing

    Set PPPres = Nothing

    Set pp = Nothing

 

End Sub

 

Hope you have any idea .. I am totally lost !

 

Marion

By: Marion

Leave a Reply

Your email address will not be published. Required fields are marked *