[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3887
  • Last Modified:

cannot hide powerpoint when manipulating through vba

I have a procedure that opens specific documents in powerpoint, performs a search and replace froma a list, then saves as another document name. The thing is it doesn't work when I set visible=false.

My concern is as the window is left open and there is no progress indicator it's a little vulnerable of accidentally being closed or modified before procedure finishes.

Speed is also an issue and but I'm not sure if this is related.

Any ideas?
Dim oPres As Presentation
    Dim oSld As Slide
    Dim oShp As Object
 
    Set ppt = CreateObject("PowerPoint.Application")
    ppt.Visible = True
    ppt.Presentations.Open strFilePath & strFileName
 
                
    'start replacing list of words in glossary subform
    With [sbfrmProjectGlossary].Form.RecordsetClone
    .MoveFirst
    Do While .EOF = False
 
    strSearchText = .TargetLanguageTerm
    If Forms![frmReplacementProject]!ReplaceOptionID = 1 Then
    strReplaceText = .TargetLanguageCorrectedTerm
    Else
    strReplaceText = strSearchText & " " & strBeforeTag & .TargetLanguageCorrectedTerm & strAfterTag
    End If
 
    Set oPres = ppt.ActivePresentation
 
        For Each oSld In oPres.Slides
            For Each oShp In oSld.Shapes
                    Call ReplaceText(oShp, strSearchText, strReplaceText)
            Next oShp
        Next oSld
    
    .MoveNext
    Loop
    End With
 
      ppt.ActivePresentation.SaveAs IncrementIfExists(strFilePath & strPrefix & strSaveExtension & strSuffix)
      DoEvents
      ppt.ActivePresentation.Close
      DoEvents
      ppt.Quit
      Set ppt = Nothing

Open in new window

0
Shawn
Asked:
Shawn
  • 8
  • 4
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Although I don't see the UserControl property ... this is from the Help File:

"When an application is launched by the user, the Visible and UserControl properties of the Application object are both set to True. When the UserControl property is set to True, it isn't possible to set the Visible property of the object to False."                                                                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

****
Here is the entire UserControl Help:
UserControl Property
See AlsoApplies ToExampleSpecificsYou can use the UserControl property to determine whether the current Microsoft Access application was started by the user or by another application with Automation, formerly called OLE Automation. Read/write Boolean.

expression.UserControl
expression    Required. An expression that returns one of the objects in the Applies To list.

Remarks
The UserControl property uses the following settings.

Setting Description
True  The current application was started by the user.
False  The current application was started by another application with Automation.

You can determine the UserControl property setting only by using Visual Basic.

This property is read-only in all views when user starts the Access application. If Microsoft Access is started by OLE Automation, the UserControl property can be set in Visual Basic.

When an application is launched by the user, the Visible and UserControl properties of the Application object are both set to True. When the UserControl property is set to True, it isn't possible to set the Visible property of the object to False.

When an Application object is created by using Automation, the Visible and UserControl properties of the object are both set to False.

Example
The following example displays a message indicating whether Access was started by the user.

MsgBox "The user started Access:  " & Application.UserControl


0
 
ShawnAuthor Commented:
tested it and it returns true. but if I understand correctly that is because it is the user manipulating Access. I think we need to look at the equivalent in Powerpoint. no??

I'm wondering if it doesn't have something to do with ActivePresentation. Maybe when we set ppt.Visible = msoFalse the presentation is no longer considered active.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I have never interfaced with PP, probably won't be of much help.

mx
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ShawnAuthor Commented:
thanks anyway mx.
0
 
Chris BottomleyCommented:
As far as I can see the problem lies with the replacement of text:  So far I can open ppt from another application without making it visible and for instance create a new presentation, put some boxes in place all without making ppt visible and it works fine.

Next step is to try in place text replacement.

Chris
0
 
Chris BottomleyCommented:
The critical line seems to be:

    ppt.Presentations.Open strFilePath & strFileName

Creating a ppt can be done invisible but at least opening via this method requires visibility.  Now need to establish alternative method(s) of opening the app but at least I understand where it fails now.

Chris
0
 
Chris BottomleyCommented:
We may have an answer?

try replacing:

    ppt.Presentations.Open strFilePath & strFileName
with
    ppt.Presentations.Open strFilePath & strFileName, , , msoFalse


Chris
0
 
Chris BottomleyCommented:
and comment out the .visible as well.

Chris
0
 
Chris BottomleyCommented:
in the database, (deleted so it would a major task to reproduce the working setup hence tested with a test harness again) of course the file is read only so perhaps:

ppt.Presentations.Open strFilePath & strFileName, msotrue,  ,  msoFalse

Chris
0
 
Chris BottomleyCommented:
I have just tried the line in the previous post in both excel 2003 and access 2003 as vba functions working on a ppt file and both work file, albeit I didn't add the references so used late binding.

Chris
0
 
ShawnAuthor Commented:
hi Chris, thanks for all the posts.

have tried your suggestions and it runs through but the new document doesn't appear in the folder after...same as before. strange.

must be something else in my code.
in the code that works for you do you have something like Set oPres = ppt.ActivePresentation ?
I'm still wondering if ActivePresentation might be guilty.

I had a similar problem in word....
    wrd.ActiveDocument.Find.Execute....didn't work
but
    wrd.Selection.Find.Execute....did work

0
 
Chris BottomleyCommented:
For test purposes:

Assuming you have a directory: c:\deleteme\ then

Running the macro pptIni will create a ppt with a single text box and some text therein.
subsequently closing that ppt and running the macro pptedit will edit the text..

To re-run, edit the mega change text back to Doris.

The significance is that it opens a ppt file using the same nmechanism and replaces text using teh same sub and it does so without the application being made visible.

Chris
Option Explicit
 
Sub pptIni()
Dim pptApp As Object
Dim pres As Object
Dim sld As Object
 
    Set pptApp = CreateObject("POWERPOINT.APPLICATION")
    Set pres = pptApp.Presentations.Add
    pres.Slides.Add 1, 12 'ppLayoutBlank
    Set sld = pres.Slides(1)
    sld.Shapes.AddTextbox msoTextOrientationHorizontal, 10, 10, 150, 100
    sld.Shapes(sld.Shapes.Count).Name = "Fred"
    sld.Shapes("fred").TextFrame.TextRange = "Fred is a super new textbox ... See!"
    sld.Shapes("fred").TextFrame.TextRange = Replace(sld.Shapes("fred").TextFrame.TextRange, "Fred", "Herbert")
    ReplaceText sld.Shapes("fred"), "Herbert", "Doris"
    
    pres.SaveAs "c:\deleteme\pres1.ppt"
    
End Sub
Sub pptEdit()
Dim pptApp As Object
Dim pres As Object
Dim sld As Object
 
    Set pptApp = CreateObject("POWERPOINT.APPLICATION")
    'pptApp.Visible = True
    pptApp.Presentations.Open "c:\deleteme\pres1.ppt", , , msoFalse
    Set pres = pptApp.Presentations(1)
    Set sld = pres.Slides(1)
    ReplaceText sld.Shapes("fred"), "Doris", "Mega Change"
    
    pres.SaveAs "c:\deleteme\pres1.ppt"
    
End Sub
Sub ReplaceText(oShp As Object, FindString As String, ReplaceString As String)
    Dim oTxtRng As Object
    Dim oTmpRng As Object
    Dim oShpTmp As Object
    Dim i As Integer
    Dim iRows As Integer
    Dim iCol As Integer
 
    On Error Resume Next
    Select Case oShp.Type
Case 19    'msoTable
    For iRows = 1 To oShp.Table.Rows.Count
        For iCol = 1 To oShp.Table.Rows(iRows).Cells.Count
            Set oShpTmp = oShp.Table.Rows(iRows).Cells(iCol).Shape
            Call ReplaceText(oShpTmp, FindString, ReplaceString)
        Next
    Next
Case msoGroup    'Groups may contain shapes with text, so look within it
    For i = 1 To oShp.GroupItems.Count
        Call ReplaceText(oShp.GroupItems(i), FindString, ReplaceString)
    Next i
Case 21    ' msoDiagram
    For i = 1 To oShp.Diagram.Nodes.Count
        Call ReplaceText(oShp.Diagram.Nodes(i).TextShape, FindString, ReplaceString)
    Next i
Case Else
    If oShp.HasTextFrame Then
        If oShp.TextFrame.HasText Then
            oShp.TextFrame.TextRange.Text = Replace(oShp.TextFrame.TextRange.Text, FindString, ReplaceString)
        End If
    Else
        For iRows = 1 To oShp.Table.Rows.Count
            For iCol = 1 To oShp.Table.Rows(iRows).Cells.Count
                Set oShpTmp = oShp.Table.Rows(iRows).Cells(iCol).Shape
                oShpTmp.TextFrame.TextRange = Replace(oShpTmp.TextFrame.TextRange.Text, FindString, _
                                          ReplaceString)
            Next
        Next
    End If
    End Select
 
End Sub

Open in new window

ppt.xls
0
 
ShawnAuthor Commented:
brilliant. Comparing your code I was able to tweak a couple of things to make it work

changed:
ppt.ActivePresentation.SaveAs    to    oPres.SaveAs
ppt.ActivePresentation.Close    to    oPres.Close

added the (1) and this is where it started working!
Set oPres = ppt.Presentations(1)

thanks again Chris
0
 
Chris BottomleyCommented:
ey up lad, tha's done well then, and i'm glad to have been of some help.

Chris
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 8
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now