question

JohnDavis-3753 avatar image
0 Votes"
JohnDavis-3753 asked JohnDavis-3753 answered

Opening Existing PowerPoint file via Excel VBA

Hi
I want to open an existing PowerPoint file (nominated by the user) using VBA within Excel. Note that I have added the PowerPoint Object library to 'References' in the macro. The code I am using (see below) I have used before successfully to open Excel files. In this instance, however, I have replaced "Workbooks.Open" with "Presentations.Open". The code successfully allows the user to browse for their file, but when the file is selected, the macro crashes at "Presentations.Open". Can someone pls tell me what I am missing? Thank you in anticipation of your help


'OPEN USER FILE
Dim my_Filename As Variant
Hello = "Browse to find your presentation. . ."
MsgBox Hello, , "POWERPOINT UPDATER"
my_Filename = Application.GetOpenFilename(filefilter:="PowerPoint Files,.ppt;.pptx")
If my_Filename <> False Then
Presentations.Open Filename:=my_Filename
End If

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
1 Vote"
TvanStiphout answered

I'm taking you literally that you only want to open a file and not subsequently manipulate it.
Then use 1 line of code, with the built-in Shell function - see help file.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohnKorchok avatar image
0 Votes"
JohnKorchok answered JohnKorchok edited

GetOpenFilename is an Excel command, not a PowerPoint command. Since you can't open a PowerPoint file in Excel, the macro crashes. Here is the PowerPoint method of opening a file from a dialog:

Sub OpenPPTX()
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select PowerPoint file"
.Filters.Add "PowerPoint file", ".pptx, .ppt" 'Add more formats if required.
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.Path & "\" 'Start looking in the folder where the workbook is.
If .Show Then
'Do further processing here
End If
End With
End Sub

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JohnDavis-3753 avatar image
0 Votes"
JohnDavis-3753 answered

Thank you both very much for taking the time to respond - I really appreciate.

Actually I need to do a great deal more than just open the PowerPoint file - but I was just trying to keep my query focused in the first instance. FYI after opening the file, I want to sequentially skip through the slides in the file, and on each slide
(i) identify charts
(ii) for each chart, open the datasheet and make various changes to the datasheet
(iii) then move the location of some shapes to re-align their location relative to the bars (or whatever) in the chart

In response to John Korchok (Hi John)....the GetOpenFilename bit works fine - it allows the user to browse for their file and select it - which allows me to identify the file name and assign the name to the variable 'my_Filename'. The problem is actually opening the file.

I CAN, however, open 'my_Filename' if I use this approach:

Sub Test_6()
'OPEN USER FILE
Hello = "Browse to find your presentation. . ."
MsgBox Hello, , "POWERPOINT UPDATER"
my_Filename = Application.GetOpenFilename(filefilter:="PowerPoint Files,.ppt;.pptx")
Dim MyPPT As Object
Set MyPPT = CreateObject("PowerPoint.Application")
MyPPT.Visible = True
MyPPT.Presentations.Open my_Filename

' I then want to sequentially skip through the slides in the file, and on each slide (i) identify charts (ii) for each chart
' open the datasheet and make various changes to the datasheet (iii) then move the location of some shapes to re-align
' their location relative to the bars (or whatever) in the chart

End Sub

But having opened the file this way, I ground to a halt in trying to even do something simple like, in the first instance, identify how many slides there are in the file by using 'ActivePresentation.Slides.Count'

So I started doing more searching and came across the concepts of 'early binding' and 'late binding' - which was a bit of revelation for a novice like me!! Based on my understanding of these concepts, if I give the Excel macro access to the PowerPoint object library, I could use 'early binding' and the macro could use all the bits in the PowerPoint library without having to things like Dim the PowerPoint file as an Object and set it using Create Object.

So logically then, it seemed that I could use GetOpenFilename to identify the name of the file, and then simply use Presentations.Open Filename:=my_Filename to open it.

So given all that, I am still at a loss as what the missing piece is. (Declaration: if it's not already obvious, my (limited) experience with VBA to date has been writing routines for Excel. This is my first foray into attempting to use VBA with PowerPoint).


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.