• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel to Powerpoint multiple data and charts on multiple slides

B

Bernz

New Member
Hi Everyone,
Can you help me? I have this code below and I want to have an auto-loop without specifying my ranges.

Right now, I need to repeat STEP 3 to STEP 8 and change MyRange to "C42:Z77", "C118:Z153"


****
Sub Excel_to_Powerpoint()
'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 = "C4:Z39" '<<<Change this range

'Step 4: Start the loop through each worksheet


Worksheets("CHS_PowerPoint").Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
Worksheets("CHS_PowerPoint").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 = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Height = 480
pp.ActiveWindow.Selection.ShapeRange.Width = 720


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

'Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing

End Sub
 
Hi Bernz ,

Your post is somewhat confusing ; you say there are 3 ranges C4:Z39 , C42:Z77 and C118:Z153.

Your step 8 comment says :

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

Which is this next worksheet ?

Are the 3 ranges you have specified on the same worksheet , or are they on different worksheets ?

If there are multiple worksheets , how many are there ?

Narayan
 
Hi NARAYANK991,

I really don't know about the steps, I just copied the VBA codes and apply it to my spreedsheet. All the data are just in one tabsheet which is named "CHS_PowerPoint". Then the ranges are just below after each data. I align all the data from column C to Z but different Rows. Before, I manually copy and paste to transfer all my data in excel into 40 slides in PPT.
 
Hi Bernz ,

OK. So there is only one worksheet ; in this you need to transfer data from 3 different ranges ; how many slides will each range occupy ? You say there are 40 slides in the presentation ; how is the data from the 3 ranges transferred to the 40 slides ?

Narayan
 
Hi NARAYANK991,

Currently, I have created 3 ranges that is equal to 3 slides on my ppt. this is my update on my VBA code. The difference on the updates is that I only change the "MyRange" of each slide. Basically, I would repeat the step 3 to 8 40 times inorder to generate my 40 slides in PPT.

Sub Excel_to_Powerpoint()
'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 = "C4:Z39" '<<<Change this range

'Step 4: Start the loop through each worksheet


Worksheets("CHS_PowerPoint").Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
Worksheets("CHS_PowerPoint").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 = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Height = 480
pp.ActiveWindow.Selection.ShapeRange.Width = 720



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

'Step 3: Set the ranges for your data and title
MyRange = "C42:Z77" '<<<Change this range

'Step 4: Start the loop through each worksheet


Worksheets("CHS_PowerPoint").Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
Worksheets("CHS_PowerPoint").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 = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Height = 480
pp.ActiveWindow.Selection.ShapeRange.Width = 720


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

'Step 3: Set the ranges for your data and title
MyRange = "C80:Z115" '<<<Change this range

'Step 4: Start the loop through each worksheet


Worksheets("CHS_PowerPoint").Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
Worksheets("CHS_PowerPoint").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 = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Height = 480
pp.ActiveWindow.Selection.ShapeRange.Width = 720


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

'Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing

End Sub
 
Hi Bernz ,

So where are these 40 range addresses available ? Do you have them listed in a range in a column somewhere in the worksheet ? If so , then you could start from the first cell of this range , and then loop through all the cells , each time using the cell contents as a range address , on these lines :

' Put the initialization code here
For i = 1 To 40
MyRange = list.Offset(i-1).Value
' The code for the first range / slide goes here
Next
' Put the cleanup code here

Narayan
 
Thanks,

Here are my ranges as of now 1st slide "C4:Z39", 2nd slide "C42:Z77", 3rd slide "C80:Z115", 4th slide "C118:Z153", 5th slide " C156:Z211", 6th slide "C214:Z269". Can you send me the full code from the start? I am still a newbie. thanks,

Bernz
 
Hi Bernz ,

I am not sure this will work , but try.
Code: 
Sub Excel_to_Powerpoint()
    Const STARTCOLUMN = 3
    Const ENDCOLUMN = 26
   
    Const STARTROW = 4
    Const NUMBEROFROWS = 36
    Const OFFSETROWS = 3

    Const MAXRANGES = 40
'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
Dim i As Integer, j As Integer

'Step 2: Open PowerPoint, add a new presentation and make visible
Set pp = CreateObject("PowerPoint.Application")
Set PPPres = pp.Presentations.Add
pp.Visible = True

i = STARTROW

Do
'Step 3: Set the ranges for your data and title
    j = i + NUMBEROFROWS - 1
    MyRange = Cells(i, STARTCOLUMN).Address & ":" & Cells(j, ENDCOLUMN).Address  '<<<Change this range

'Step 4: Start the loop through each worksheet
Worksheets("CHS_PowerPoint").Select
Application.Wait (Now + TimeValue("0:00:1"))

'Step 5: Copy the range as picture
Worksheets("CHS_PowerPoint").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 = 0
pp.ActiveWindow.Selection.ShapeRange.Left = 0
pp.ActiveWindow.Selection.ShapeRange.Height = 480
pp.ActiveWindow.Selection.ShapeRange.Width = 720

'Step 8: Add the title to the slide then move to next worksheet
i = j + OFFSETROWS
k = k + 1
Loop Until k > MAXRANGES

'Step 9: Memory Cleanup
pp.Activate
Set PPSlide = Nothing
Set PPPres = Nothing
Set pp = Nothing
End Sub
Narayan
 
Hi Narayan,

It worked!!!:DD:)

Thanks, I will just to create a fix range and cell distance from each other in order to create the slides.

Thank you so much for you help!.

Bernz
 
You must log in or register to reply here.
Back
Top