- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Updating picture links in a PowerPoint document using JSL and VBA
I am working on an add-in to automate some reporting.
- There is a report template PowerPoint file that includes links to image files of various charts and tables. (see attched)
- When the user clicks the "add to active report" button, the appropriate image file will be overwritten so that the new chart or table will show up in the report.
The problem I am having is that I need to update the links in the PowerPoint document. I would like to be able to say "create new report", copy the template and default images into a new folder, and update the links in the the new PowerPoint file so that the point to the image files in the new folder instead of the image files in the template folder. Any ideas how I might do this?
Many thanks,
John
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Updating picture links in a PowerPoint document using JSL and VBA
Hi @johnmoore, I faced something similar, I know the difficulty to keep updating packages using corporate templates in power point and need to update about 300 images from Anova, variability charts, Cpk reports, etc created in JMP where every power point package contains "basically the same charts", always in the same place, the only difference is the raw data needed for the specific report.
I am not really expert in VBA, but when you need something, you keep searching every where and do trial and error until you can make/find a solution.
This is the approach I follow, maybe you have something similar so far:
- In JMP send through JSL your analysis (images) to a folder in your PC and assign a specific name to each image (never name them same because then only will overwrite). Once you customize in power point the location of each specific image and make the link for each of them.save it as a macro file (this will be your template).
- Open your template and Run the macro any time you need a new report.
- Finally save the file as no macro and with a different name to keep the template with images ready for next time.
Hope this can help you, I strongly recommend you to put password to the macro to avoid changes in the script.
Regards.
Sub LinkedGraphsToPictures()
'Created by Andres Gonzalez, March 2019
'Purpose: Change linked graphs into regular images
Dim shp As Shape
Dim sld As Slide
Dim pic As Shape
Dim shp_left As Double
Dim shp_top As Double
Dim sld_count As Long
'Loop Through Each Slide in ActivePresentation
For Each sld In ActivePresentation.Slides
For sld_count = ActivePresentation.Slides.Count To 1 Step -1
' Count to -1 to countbackward in order to avoid skipping charts due to chart deleted
Set sld = ActivePresentation.Slides(sld_count)
For Each shp In sld.Shapes
If shp.Type = msoLinkedPicture Then
'Retrieve current positioning
shp_left = shp.Left
shp_top = shp.Top
'Copy/Paste as Picture
shp.Copy
sld.Shapes.PasteSpecial DataType:=ppPastePNG
Set pic = sld.Shapes(sld.Shapes.Count)
'Delete Linked Shape
shp.Delete
'Reposition newly pasted picture
pic.Left = shp_left
pic.Top = shp_top
End If
Next shp
Next sld_count
Next sld
End Sub
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: Updating picture links in a PowerPoint document using JSL and VBA
Hi @johnmoore, I faced something similar, I know the difficulty to keep updating packages using corporate templates in power point and need to update about 300 images from Anova, variability charts, Cpk reports, etc created in JMP where every power point package contains "basically the same charts", always in the same place, the only difference is the raw data needed for the specific report.
I am not really expert in VBA, but when you need something, you keep searching every where and do trial and error until you can make/find a solution.
This is the approach I follow, maybe you have something similar so far:
- In JMP send through JSL your analysis (images) to a folder in your PC and assign a specific name to each image (never name them same because then only will overwrite). Once you customize in power point the location of each specific image and make the link for each of them.save it as a macro file (this will be your template).
- Open your template and Run the macro any time you need a new report.
- Finally save the file as no macro and with a different name to keep the template with images ready for next time.
Hope this can help you, I strongly recommend you to put password to the macro to avoid changes in the script.
Regards.
Sub LinkedGraphsToPictures()
'Created by Andres Gonzalez, March 2019
'Purpose: Change linked graphs into regular images
Dim shp As Shape
Dim sld As Slide
Dim pic As Shape
Dim shp_left As Double
Dim shp_top As Double
Dim sld_count As Long
'Loop Through Each Slide in ActivePresentation
For Each sld In ActivePresentation.Slides
For sld_count = ActivePresentation.Slides.Count To 1 Step -1
' Count to -1 to countbackward in order to avoid skipping charts due to chart deleted
Set sld = ActivePresentation.Slides(sld_count)
For Each shp In sld.Shapes
If shp.Type = msoLinkedPicture Then
'Retrieve current positioning
shp_left = shp.Left
shp_top = shp.Top
'Copy/Paste as Picture
shp.Copy
sld.Shapes.PasteSpecial DataType:=ppPastePNG
Set pic = sld.Shapes(sld.Shapes.Count)
'Delete Linked Shape
shp.Delete
'Reposition newly pasted picture
pic.Left = shp_left
pic.Top = shp_top
End If
Next shp
Next sld_count
Next sld
End Sub