PDA

View Full Version : Need help getting started - copying a bunch of cells to a slide as different shapes.



drez
09-23-2008, 11:29 AM
Hi,

I'm kind of new to PowerPoint programming. I'm familiar with Excel and Access VBA (to a point) and I'm sure I could figure this out eventually but I'm kind of in a rush to get some kind of makeshift solution in place for Excel to PowerPoint exporting and I was wondering if someone could set me in the right direction.

I've already done some googling and found out how to open up a PowerPoint object from within the Excel VBE, and how to open an Excel object from within PowerPoint. What I'd like to do those is the following
I have a bunch of spreadsheets with tables. Each table has certain data that needs to exist as its own shape on a single PowerPoint slide. Let's say the relevant data is in columna B, D, G, K, L, and M, and on the slide the data in these cells should exist as TEXT in six different shapes. The end user can reposition and format them, but getting the data on the sheet is the most important part. If I can reposition the shapes after that using VBA, that'd be good too.

I'm sure all of this can be done, I'm just not sure where to begin or what to look up to figure this out. Given that the powerpoint already exists with the correct number of slides, the best case scenario would be this from a procedural standpoint:

A - Look at row in Excel
B - Copy data from columns B, D, G, K, L, and M in this row
C - Paste these items as separate shapes on the current active slide
D - Skip TWO slides (I need a placeholder after each slide with data)
F - Focus on next row in Excel
G - Goto "A" until there are no more rows on current worksheet
Whether this is done from Excel by exporting to PowerPoint or done in PowerPoint by importing from Excel doesn't matter much, though I feel more comfortable writing within an Excel environment at present.

Can anyone help?

edit: Another thing that would be helpful would be to add bullets (and then a single space) to the beginning of each line of text within the shapes this code generates. In most cases, only only one line of text would be in each shape, but even so, adding bullets on the fly would be a good thing to have in the code.

got2surf2
09-24-2008, 01:15 PM
Here's some code I wrote to take data in excel and create a sequence of rectangles in a ppt slide.....it is an Excel VBA script. Could be a starting point for you....

' Start PowerPoint.
Dim ppApp As Object
Set ppApp = CreateObject("Powerpoint.Application.12")

' Make it visible.
ppApp.Visible = True
' Add a new presentation.
Dim ppPres As Object
Set ppPres = ppApp.Presentations.Add(msoTrue)
ppPres.ApplyTemplate ("F:\users\sanjay\documents\kaizen\datanautix\templates\DNppt2.potm")

' Add a new slide.
Dim ppSlide1 As Object
Set ppSlide1 = ppPres.Slides.Add(1, ppLayoutTitleOnly)

' Add some text.
ppSlide1.Shapes(1).TextFrame.TextRange.Text = "Call Flow Map" & " - Call ID " & Range("c1").Value

Dim StartPoint, Width As Integer
i1 = 2
DataThere = Range("A4").Value StartPoint = 60
Do While DataThere <> ""
Width = Range("c4").Value
ppSlide1.Shapes.AddShape 1, StartPoint, 75, Width, 100
ppSlide1.Shapes.AddShape 1, StartPoint, 175, Width, 80
ppSlide1.Shapes(i1).Fill.ForeColor.RGB = RGB(Range("f4").Value, Range("g4").Value, Range("h4").Value)
ppSlide1.Shapes(i1).Fill.Transparency = 0.5
Range("A4:H4").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
DataThere = Range("A4").Value
StartPoint = StartPoint + Width
i1 = i1 + 1
Loop