I have this excel file that works as a data base, there i fill everyday 5 tables in 5 different worksheets, each table grows along with the day, so each row is linked to an specific date.
Then i need to make a daily report in a powerpoint presentation. I've done the presentation as a template, putting images of the tables and then embedding labels in everyplace i need.
Now the request, I need to do a vba program that take the value of one cell that is in the same row as some specific date (that i can define) and then put it in a label on a existing powerpoint presentation. After that i need that the these changes will be saved in a new file with the date as file name.
I hope i explained myself clear enough because I'm not native English speaker.
I'm using office 2003. All the help will be appreciated
I would like to have a macro that when starts asks for the date that i want the report to linked to, then check it with the data within the excel file and after that send it to the respective powerpoint label.
As a start it would be helpeful some code that can take one value from excel and then put it in a label embedded label on a powerppoint presentation. I think I can manage the rest, while i'm learning VBA.
I have a worksheet with dates in column A, starting in row 2, and some "values" in column B. This code will ask for a date to be input, find it in column A, get its associated value from column B, then open a pre-existing powerpoint presentation and place the value into a textbox in the first slide:
Code:
Private Sub cmdExport_Click()
Dim resp As Date
Dim ws As Worksheet
Dim lr As Long
Dim j As Long
Dim val As String
Dim ppt As PowerPoint.Application
Dim pres As PowerPoint.Presentation
Dim slide As PowerPoint.slide
Dim tb As PowerPoint.Shape
Set ws = ActiveSheet
lr = ws.Range("a" & Rows.Count).End(xlUp).Row 'last row of data in column A
resp = InputBox("Enter date in m/dd/yy format") 'input the date
For j = 2 To lr
If ws.Range("a" & j).Value = resp Then 'found matching date in column A
val = ws.Range("b" & j).Value 'get value from same row, column B
Exit For
End If
Next j
Set ppt = CreateObject("PowerPoint.Application")
ppt.Visible = True
If val <> "" Then
Dim strPath As String
strPath = "c:\yourPath\pp1.pptx" 'change this
Set pres = ppt.Presentations.Open(strPath)
Set slide = pres.Slides(1) 'first slide
Set tb = slide.Shapes("TextBox 1")
tb.TextFrame2.TextRange.Characters.Text = val 'put "val" into textbox 1
End If
'save...close...etc.
End Sub
I read it all and I rhink that helps a lot, but when i run it stops highlighting in gray "ppt As PowerPoint.Application" and showing this message (translated from spanish) "Compilation error" & "Has not been define the define type by the user".
I found the solution and didn't notice your answer, thanks anyway.
Now it's stopping again, it highlights in gray the word "TextFrame2" (from the line "tb.TextFrame2.TextRange.Characters.Text = val") an shows a message box saying "Compilation error:" & "has not been found the method or the member data".
Ok, I checked the intellisense and then the only thing that worked was to supress the number 2 after "tb.textFrame2".
After that, I ran it again and then came a message box saying "runtime error: -2147188160 (80048240)" & "textframe unknown member: invalid request. This type of shape cannot have a TextRange".
I tried adding the number 2 again but still the same problem as in earlier posts.
I tried both "...Slide1.Textbox1.Value..." and "tb.TextFrame.TextRange.Text="something"" but neither one have succeed. I just downloaded the Office 2003 Editions: PowerPoint VBA Language Reference and i'll try to see if I can get something from it.
I also checked that the code can actually can manipulate the "textbox1", modifying height and widht, among others, but didn't find the way to change the text.
There are two files, the first "Presentacion.ppt" is the one the i'm testing with... the second one "Propuesta INFORME GENERAL PRODUCCION 3" is the one the i really need to work with, it has 148 labels that ought to be change through the code.
The shape on the sample presentation slide is "type 12." I'm not sure what that constant refers to.
Tin, do you know?
It is an activeX TextBox. Kind of what I figured when I posted #12 above.
Something like this should work.
Code:
Dim tb As Shape
Set tb = pres.Slides(1).Shapes("TextBox1")
If tb.Type = msoOLEControlObject Then
'got it
tb.OLEFormat.Object.Text = "hi"
Else
' ohoh now what do I do?
End If
Last edited by TnTinMN; Mar 21st, 2014 at 02:10 PM.