R
Robert Smith
I'm trying to create script that will read a Tab-delimited file and create a
presentation based on that content. I'm stuck on 2 things:
1) inserting graphics into the slides -- can't figure out the right syntax
2) On one text object, how to add hypelink to just one line instead of
everything in that object.
Everything below works EXCEPT for the two issues mentioned above.
===============================
'On Error Resume Next
'Need CODE HERE - Get File Location
'strPathtoTextFile = InputBox ("Please enter file location path:" & vbCrLF &
vbCrLF & "C:\temp\resi_bbe\")
strPathtoTextFile = "C:\temp\resi_bbe\"
'===========================================================================
' Connect to listings.txt file
'===========================================================================
Set objPPT = CreateObject("PowerPoint.Application")
Set objPresentation = objPPT.Presentations.Add
'objPresentation.ApplyTemplate("C:\Program Files\Microsoft
Office\Templates\Presentation Designs\Globe.pot")
'objPresentation.ApplyTemplate("C:\Documents and Settings\Robert\My
Documents\templates\RobertGraySmith.pot")
'===========================================================================
'On Error Resume Next
'Set to Tab Delimited
'Need CODE HERE - Get File Location - Create SCHEMA.INI file
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'===========================================================================
' Set Schema file for Tab Delimited
'===========================================================================
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strPathtoTextFile & "schema.ini")
objFile.WriteLine("[listing.txt]" & vbCrLf & "Format=TabDelimited")
objFile.Close
'===========================================================================
' Connect to listings.txt file
'===========================================================================
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
objRecordset.Open "SELECT * FROM listing.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'===========================================================================
' Create Powerpoint Slides
'===========================================================================
'**** CONTENT SLIDES ****
Do Until objRecordset.EOF
'change style by () - eg: Slides.Add(X,9) = ppLayoutTextAndObject
Set objSlide = objPresentation.Slides.Add(1, 9)
Set objShapes = objSlide.Shapes
Set objTitle = objShapes.Item(1)
objTitle.TextFrame.TextRange.Text = objRecordset.Fields.Item("Address")
&_
", " & objRecordset.Fields.Item("City Name") & vbTab &_
"$" & objRecordset.Fields.Item("List Price")
'======> Argh...I want the first item to be hyperlinked but not all the
following items.
Set objTitle = objShapes.Item(2)
objTitle.TextFrame.TextRange.Text = objRecordset.Fields.Item("ML
Number") &_
Chr(13) & objRecordset.Fields.Item("Bedrooms") & " Bedrooms"
With objTitle.TextFrame.TextRange.ActionSettings(1).Hyperlink
.Address = "http://www.robertgsmith.com/" &
objRecordset.Fields.Item("ML Number")
.SubAddress = ""
.ScreenTip = "See listing detail"
End With
'======> How do I insert a picture into the slide?
'Set objPicture = objShapes.Item(3)
'objPicture.addShape ("C:\temp\RESI_BBE\24121779.jpg")
'objPicture.Shapes.AddPicture = "filename=C:\temp\RESI_BBE\24121779.jpg"
objRecordset.MoveNext
Loop
'**** ADD TITLE SLIDE ****
Set objSlide = objPresentation.Slides.Add(1, 1)
Set objShapes = objSlide.Shapes
Set objTitle = objShapes.Item(1)
objTitle.TextFrame.TextRange.Text = "Main Title"
Set objTitle = objShapes.Item(2)
objTitle.TextFrame.TextRange.Text = "Sub-Title"
'===========================================================================
' Finish and show Powerpoint
'===========================================================================
objPPT.Visible = True
'objPresentation.SaveAs("C:\test.ppt")
'objPresentation.Close
'objPPT.Quit
presentation based on that content. I'm stuck on 2 things:
1) inserting graphics into the slides -- can't figure out the right syntax
2) On one text object, how to add hypelink to just one line instead of
everything in that object.
Everything below works EXCEPT for the two issues mentioned above.
===============================
'On Error Resume Next
'Need CODE HERE - Get File Location
'strPathtoTextFile = InputBox ("Please enter file location path:" & vbCrLF &
vbCrLF & "C:\temp\resi_bbe\")
strPathtoTextFile = "C:\temp\resi_bbe\"
'===========================================================================
' Connect to listings.txt file
'===========================================================================
Set objPPT = CreateObject("PowerPoint.Application")
Set objPresentation = objPPT.Presentations.Add
'objPresentation.ApplyTemplate("C:\Program Files\Microsoft
Office\Templates\Presentation Designs\Globe.pot")
'objPresentation.ApplyTemplate("C:\Documents and Settings\Robert\My
Documents\templates\RobertGraySmith.pot")
'===========================================================================
'On Error Resume Next
'Set to Tab Delimited
'Need CODE HERE - Get File Location - Create SCHEMA.INI file
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'===========================================================================
' Set Schema file for Tab Delimited
'===========================================================================
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(strPathtoTextFile & "schema.ini")
objFile.WriteLine("[listing.txt]" & vbCrLf & "Format=TabDelimited")
objFile.Close
'===========================================================================
' Connect to listings.txt file
'===========================================================================
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""
objRecordset.Open "SELECT * FROM listing.txt", _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
'===========================================================================
' Create Powerpoint Slides
'===========================================================================
'**** CONTENT SLIDES ****
Do Until objRecordset.EOF
'change style by () - eg: Slides.Add(X,9) = ppLayoutTextAndObject
Set objSlide = objPresentation.Slides.Add(1, 9)
Set objShapes = objSlide.Shapes
Set objTitle = objShapes.Item(1)
objTitle.TextFrame.TextRange.Text = objRecordset.Fields.Item("Address")
&_
", " & objRecordset.Fields.Item("City Name") & vbTab &_
"$" & objRecordset.Fields.Item("List Price")
'======> Argh...I want the first item to be hyperlinked but not all the
following items.
Set objTitle = objShapes.Item(2)
objTitle.TextFrame.TextRange.Text = objRecordset.Fields.Item("ML
Number") &_
Chr(13) & objRecordset.Fields.Item("Bedrooms") & " Bedrooms"
With objTitle.TextFrame.TextRange.ActionSettings(1).Hyperlink
.Address = "http://www.robertgsmith.com/" &
objRecordset.Fields.Item("ML Number")
.SubAddress = ""
.ScreenTip = "See listing detail"
End With
'======> How do I insert a picture into the slide?
'Set objPicture = objShapes.Item(3)
'objPicture.addShape ("C:\temp\RESI_BBE\24121779.jpg")
'objPicture.Shapes.AddPicture = "filename=C:\temp\RESI_BBE\24121779.jpg"
objRecordset.MoveNext
Loop
'**** ADD TITLE SLIDE ****
Set objSlide = objPresentation.Slides.Add(1, 1)
Set objShapes = objSlide.Shapes
Set objTitle = objShapes.Item(1)
objTitle.TextFrame.TextRange.Text = "Main Title"
Set objTitle = objShapes.Item(2)
objTitle.TextFrame.TextRange.Text = "Sub-Title"
'===========================================================================
' Finish and show Powerpoint
'===========================================================================
objPPT.Visible = True
'objPresentation.SaveAs("C:\test.ppt")
'objPresentation.Close
'objPPT.Quit