Excel => PowerPoint automation - Excel looses focus

[JorgenB]

Hi,

I've been working with VBA in Excel for some months and finds it to be
a very powerful and useful environment. I'm learing a lot from reading
Q&A's in Google and Microsoft groups - it's really great!

Now I'm trying something new: I want to copy some stuff (Text and
Graphs) from Excel to Powerpoint. It works basically fine, except for
one irritating feature: When I make PowerPoint visible, I loose the
focus I still need in the Excel window.

My code starts like this:

Sub XL2PPT()
' using OLE automation
'---------------------
Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim i As Integer, cnt As Integer
Dim mfile As String, filetoopen As Variant
Dim sht As String
Dim expsht As Worksheet, mrow As Integer

Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible

ThisWorkbook.Activate

filetoopen = Application _
.GetOpenFilename("PowerPoint files (*.ppt), *.ppt", , "Please select
the relevant PowerPoint report template")

If filetoopen = False Then
MsgBox "No file was selected"
Exit Sub
End If

Set Pres = PPT.Presentations.Open(Filename:=filetoopen,
ReadOnly:=msoFalse)

' --------- and so on

What I want is to keep focus in my Excel application, but what seems to
happen is that the PowerPoint window takes over and the Excel window
starts blinking, because there is an active dialogue asking to open a
template into which I'm writing the stuff from Excel. When selecting
the Excel window again everything works just fine, but I think that the
users should not be bothered with this confusing extra trouble.

Do any of you experienced gurus know how I can achieve focus to my
Excel window again using VBA/OLE (obviously ThisWorkbook.Activate does
not make it)?

/JorgenB

 

[Shyam Pillai]

Hi,
Why do you want to make PowerPoint visible right away? Keep automating it
while it is invisible when done with automation make it visible. That way
the user sees only the finished state and not the intermediate ones and not
to mention the speed gain by doing so.

 

[JorgenB]

Hi Shyam,

Thank you for your answer. I have learnt quite some good PPT VBA stuff
from your good answeres on Google!

But when trying this particular tip, which makes sense for me, I still
get an error when executing the code.

The debugger halts at the following line:

Set Pres = PPT.Presentations.Open(Filename:=filetoopen,
ReadOnly:=msoFalse)

The error message says that the request to PowerPoint is invalid, and
that the Frame Window does not exist.

So, to move the statement PPT.Visible = True
- just before this line wold work, but I'm then seeing the powerpoint
slides being added for a while until the automation job is finished.

So I guess there is another error that I'm making here..:-(

BR
JorgenB

Shyam Pillai skrev: