Auto populate powerpoint text boxes

**Newibie_VB8** · 08-09-2012, 12:40 AM

Wondering if anyone could help me, the below code is written in powerpoint to update text boxes which i have named based on an array of excel cells. I have two questions???

1) how can i make the excel file reference so that I can send a power point and an excel file to someone and they can use this? Meaning how do i reference the excel application without using the "C:\\smaurer" (my user name) file ??

2) can i make this any shorter of a code? I literally had to name 140 textboxes, is ther someway to tell it to fill the next text box?

This code was for powerpoint so it is different from excel like I am used to, any input would be aprreciated!!!??!!!?!?!!?!?!?

Private Sub CommandButton1_Click()
Dim I As Integer
Dim oXL As Object 'Excel.Application
Dim oWB As Object 'Excel.Workbook
Dim oSld As Slide
Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.Workbooks.Open(FileName:="C:\Users\smaurer\Documents\textbox_auto_copy_template.xlsx")
'Assumes the active presentation has 40 slides.
For I = 3 To 3
Set oSld = ActivePresentation.Slides(I)
'Assumes that the first 9 shapes on the slide are the shapes which map to the cells in excel
' Alternately you could use shape names to put them in the right textbox
' e.g. oSld.Shapes("Textbox 10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("A" & CStr(I)).Value
' Copy cell contents from the 1st sheet in Excel to the textboxes in PowerPoint.
 
oSld.Shapes("Name1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B5:B5").Value
oSld.Shapes("Name2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B6:B6").Value
oSld.Shapes("Name3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B7:B7").Value
oSld.Shapes("Name4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B8:B8").Value
oSld.Shapes("Name5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B9:B9").Value
oSld.Shapes("Name6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B10:B10").Value
oSld.Shapes("Name7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B11:B11").Value
oSld.Shapes("Name8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B12:B12").Value
oSld.Shapes("Name9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B13:B13").Value
oSld.Shapes("Name10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B14:B14").Value
oSld.Shapes("Name11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B15:B15").Value
oSld.Shapes("Name12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B16:B16").Value
oSld.Shapes("Name13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B17:B17").Value
oSld.Shapes("Name14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("B18:B18").Value
 
oSld.Shapes("Col1Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C5:C5").Value
oSld.Shapes("Col1Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C6:C6").Value
oSld.Shapes("Col1Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C7:C7").Value
oSld.Shapes("Col1Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C8:C8").Value
oSld.Shapes("Col1Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C9:C9").Value
oSld.Shapes("Col1Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C10:C10").Value
oSld.Shapes("Col1Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C11:C11").Value
oSld.Shapes("Col1Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C12:C12").Value
oSld.Shapes("Col1Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C13:C13").Value
oSld.Shapes("Col1Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C14:C14").Value
oSld.Shapes("Col1Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C15:C15").Value
oSld.Shapes("Col1Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C16:C16").Value
oSld.Shapes("Col1Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C17:C17").Value
oSld.Shapes("Col1Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("C18:C18").Value
 
oSld.Shapes("Col2Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D5:D5").Value
oSld.Shapes("Col2Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D6:D6").Value
oSld.Shapes("Col2Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D7:D7").Value
oSld.Shapes("Col2Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D8:D8").Value
oSld.Shapes("Col2Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D9:D9").Value
oSld.Shapes("Col2Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D10:D10").Value
oSld.Shapes("Col2Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D11:D11").Value
oSld.Shapes("Col2Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D12:D12").Value
oSld.Shapes("Col2Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D13:D13").Value
oSld.Shapes("Col2Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D14:D14").Value
oSld.Shapes("Col2Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D15:D15").Value
oSld.Shapes("Col2Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D16:D16").Value
oSld.Shapes("Col2Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D17:D17").Value
oSld.Shapes("Col2Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("D18:D18").Value
 
oSld.Shapes("Col3Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E5:E5").Value
oSld.Shapes("Col3Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E6:E6").Value
oSld.Shapes("Col3Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E7:E7").Value
oSld.Shapes("Col3Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E8:E8").Value
oSld.Shapes("Col3Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E9:E9").Value
oSld.Shapes("Col3Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E10:E10").Value
oSld.Shapes("Col3Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E11:E11").Value
oSld.Shapes("Col3Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E12:E12").Value
oSld.Shapes("Col3Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E13:E13").Value
oSld.Shapes("Col3Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E14:E14").Value
oSld.Shapes("Col3Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E15:E15").Value
oSld.Shapes("Col3Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E16:E16").Value
oSld.Shapes("Col3Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E17:E17").Value
oSld.Shapes("Col3Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("E18:E18").Value
 
 
oSld.Shapes("Col4Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F5:F5").Value
oSld.Shapes("Col4Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F6:F6").Value
oSld.Shapes("Col4Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F7:F7").Value
oSld.Shapes("Col4Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F8:F8").Value
oSld.Shapes("Col4Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F9:F9").Value
oSld.Shapes("Col4Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F10:F10").Value
oSld.Shapes("Col4Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F11:F11").Value
oSld.Shapes("Col4Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F12:F12").Value
oSld.Shapes("Col4Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F13:F13").Value
oSld.Shapes("Col4Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F14:F14").Value
oSld.Shapes("Col4Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F15:F15").Value
oSld.Shapes("Col4Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F16:F16").Value
oSld.Shapes("Col4Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F17:F17").Value
oSld.Shapes("Col4Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("F18:F18").Value
 
oSld.Shapes("Name15").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G5:G5").Value
oSld.Shapes("Name16").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G6:G6").Value
oSld.Shapes("Name17").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G7:G7").Value
oSld.Shapes("Name18").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G8:G8").Value
oSld.Shapes("Name19").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G9:G9").Value
oSld.Shapes("Name20").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G10:G10").Value
oSld.Shapes("Name21").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G11:G11").Value
oSld.Shapes("Name22").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G12:G12").Value
oSld.Shapes("Name23").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G13:G13").Value
oSld.Shapes("Name24").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G14:G14").Value
oSld.Shapes("Name25").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G15:G15").Value
oSld.Shapes("Name26").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G16:G16").Value
oSld.Shapes("Name27").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G17:G17").Value
oSld.Shapes("Name28").TextFrame.TextRange.Text = oWB.Sheets(1).Range("G18:G18").Value
 
oSld.Shapes("Col5Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H5:H5").Value
oSld.Shapes("Col5Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H6:H6").Value
oSld.Shapes("Col5Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H7:H7").Value
oSld.Shapes("Col5Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H8:H8").Value
oSld.Shapes("Col5Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H9:H9").Value
oSld.Shapes("Col5Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H10:H10").Value
oSld.Shapes("Col5Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H11:H11").Value
oSld.Shapes("Col5Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H12:H12").Value
oSld.Shapes("Col5Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H13:H13").Value
oSld.Shapes("Col5Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H14:H14").Value
oSld.Shapes("Col5Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H15:H15").Value
oSld.Shapes("Col5Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H16:H16").Value
oSld.Shapes("Col5Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H17:H17").Value
oSld.Shapes("Col5Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("H18:H18").Value
 
oSld.Shapes("Col6Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I5:I5").Value
oSld.Shapes("Col6Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I6:I6").Value
oSld.Shapes("Col6Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I7:I7").Value
oSld.Shapes("Col6Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I8:I8").Value
oSld.Shapes("Col6Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I9:I9").Value
oSld.Shapes("Col6Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I10:I10").Value
oSld.Shapes("Col6Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I11:I11").Value
oSld.Shapes("Col6Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I12:I12").Value
oSld.Shapes("Col6Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I13:I13").Value
oSld.Shapes("Col6Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I14:I14").Value
oSld.Shapes("Col6Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I15:I15").Value
oSld.Shapes("Col6Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I16:I16").Value
oSld.Shapes("Col6Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I17:I17").Value
oSld.Shapes("Col6Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("I18:I18").Value
 
oSld.Shapes("Col7Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J5:J5").Value
oSld.Shapes("Col7Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J6:J6").Value
oSld.Shapes("Col7Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J7:J7").Value
oSld.Shapes("Col7Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J8:J8").Value
oSld.Shapes("Col7Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J9:J9").Value
oSld.Shapes("Col7Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J10:J10").Value
oSld.Shapes("Col7Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J11:J11").Value
oSld.Shapes("Col7Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J12:J12").Value
oSld.Shapes("Col7Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J13:J13").Value
oSld.Shapes("Col7Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J14:J14").Value
oSld.Shapes("Col7Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J15:J15").Value
oSld.Shapes("Col7Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J16:J16").Value
oSld.Shapes("Col7Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J17:J17").Value
oSld.Shapes("Col7Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("J18:J18").Value
 
oSld.Shapes("Col8Row1").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K5:K5").Value
oSld.Shapes("Col8Row2").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K6:K6").Value
oSld.Shapes("Col8Row3").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K7:K7").Value
oSld.Shapes("Col8Row4").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K8:K8").Value
oSld.Shapes("Col8Row5").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K9:K9").Value
oSld.Shapes("Col8Row6").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K10:K10").Value
oSld.Shapes("Col8Row7").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K11:K11").Value
oSld.Shapes("Col8Row8").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K12:K12").Value
oSld.Shapes("Col8Row9").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K13:K13").Value
oSld.Shapes("Col8Row10").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K14:K14").Value
oSld.Shapes("Col8Row11").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K15:K15").Value
oSld.Shapes("Col8Row12").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K16:K16").Value
oSld.Shapes("Col8Row13").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K17:K17").Value
oSld.Shapes("Col8Row14").TextFrame.TextRange.Text = oWB.Sheets(1).Range("K18:K18").Value
 
Next
oWB.Close
oXL.Quit
Set oWB = Nothing
Set oXL = Nothing
End Sub

**Cosmo** · 08-09-2012, 09:10 AM

One suggestion to reduce the length of code is to use a loop to set the values:

dim cellNumber as integer
dim j as integer
for j = 1 to 14
cellNumber = j + 4
oSld.Shapes("Name" + j).TextFrame.TextRange.Text = oWB.Sheets(1).Range("B" + cellNumber + ":B" + cellNumber).Value
oSld.Shapes("Col1Row" + j).TextFrame.TextRange.Text = oWB.Sheets(1).Range("C" + cellNumber + ":C" + cellNumber).Value
...
oSld.Shapes("Col8Row" + j).TextFrame.TextRange.Text = oWB.Sheets(1).Range("K" + cellNumber + ":K" + cellNumber).Value
next i

**John Wilson** · 08-09-2012, 10:00 AM

From your naming it sounds like you should be using a table in PowerPoint with 14 rows and 8 columns. This would be easier to populate in vba as you can loop through the cells.

**Newibie_VB8** · 08-11-2012, 11:23 AM

Hi Guys,

John - I am using the text boxes because where I work they really like the format of the slide with the individual text boxes not the table

Cosmo - I have been trying really hard to work in the loop, but keep getting a type mismatch error.

I have simplified the example to better gain an understanding of this. Really new to VBA and an intern at my work, I have to do a presentation on how I figured out how to auto copy the excel info (what i did with that hideously long code) I really want to clean up the code with loop so i don't look like such an idiot!

Here is the simplified example.

I have 4 text boxes named (Name1, Name2, Name3, Name4)
I have excel cells (B1, B2, B3, B4)

I want to put the contents of the cells into the textboxes using a loop.

Here is what I have and keep getting a type mixmatch error, can someone help me out on this?

Sub TestLoop()
Dim oXL As Object 'Excel.Application
Dim oWB As Object 'Excel.Workbook
Dim oSld As Slide
Set oXL = CreateObject("Excel.Application")
Set oWB = oXL.Workbooks.Open(FileName:="C:\Users\smaurer\Documents\AutoFillpptTextBox\VBA_Test_loop.xlsx")
Set oSld = ActivePresentation.Slides(1)
Dim j As Integer
For j = 1 To 4
oSld.Shapes("Name" + j).TextFrame.TextRange.Text = oWB.Sheets(1).Range("B" + j).Value
Next j
oWB.Close
oXL.Quit
End Sub

Thread: Auto populate powerpoint text boxes

Thread Tools

Display

Auto populate powerpoint text boxes

Posting Permissions