Results 1 to 12 of 12
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    From the help with code I found here in the Lounge, I was able to create a subroutine which copies a data table from 12 worksheets to 12 new slides in a PPT file. I open a new PPT and add 12 new slides, and change the title.

    The problem is that it only works if I step through the VBA from Excel (set breakpoint, use F8 to single step). I get an error about some window not being active if I let it run on its own. I added a comment next to the line which fails. The code is activated through a button on a worksheet. The workbook has one master sheet containing names and ID codes. The other 12 worksheets are created by another set of code whichworks great.

    The code segment that creates and populates the PPT file is this:
    [pre]
    Set objPPT = GetObject(, "PowerPoint.Application")

    If objPPT Is Nothing Then
    Set objPPT = CreateObject("PowerPoint.Application")

    If objPPT Is Nothing Then
    MsgBox "Can not start PowerPoint", vbExclamation
    Exit Sub
    End If
    blnStart = True
    End If

    On Error GoTo ErrHandler

    ' create new PPT file
    Application.ActivateMicrosoftApp (xlMicrosoftPowerPoint)

    Dim rng As Range, k As Integer, shp As PowerPoint.Shapes

    ' insert one slide per table in ascending order

    For k = MAX_GRADE To 1 Step -1
    Set rng = ThisWorkbook.Worksheets("T" & k).Range(GRID_SIZE)
    rng.CopyPicture

    With objPPT.ActiveWindow '++++ fails if Excel is not active
    .ViewType = ppViewSlide
    .Presentation.Slides.Add(Index:=1, Layout:=ppLayoutTitleOnly).Select
    .View.Paste

    ' move the pasted table down a bit
    .Selection.ShapeRange.IncrementTop 34

    ' add title
    Set shp = .Selection.SlideRange.Shapes ' only 2 shapes on this slide
    shp(1).TextFrame.TextRange.Characters(1, 0).Text = "Grade Level " & Mid(rng.Parent.Name, 2)
    Set shp = Nothing

    End With
    Next k
    [/pre]
    How can I change this such that it leaves Excel as the active application to avoid the error?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think you can omit this line of code:

    Application.ActivateMicrosoftApp (xlMicrosoftPowerPoint)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='pieterse' post='765545' date='15-Mar-2009 22:59']I think you can omit this line of code:

    Application.ActivateMicrosoftApp (xlMicrosoftPowerPoint)[/quote]

    I removed that line and it generates an error even earlier. It says "Application (unknown member): Invalid request. There is no currently active document window."

    I have attached the full workbook and code (it's not much) in case you have time to look into it more. Given it works if I single step through the VBA, that seems to indicate that I need Excel to be the active application and not PowerPoint when it's running. Most of the code came from a post that prompted the user to open an existing PPT file, add a single slide, save/close it and quit PPT. This is way more than I need to do.

    I need it to create a new PPT file, add 12 slides at the beginning. Each slide contains a table from one of 12 worksheets which is pasted as a picture. The PPT file is left open and unsaved when it's done so the user can finish whatever else they want to do in that PPT file.

    There is only one code Module, ProcessNames. The subroutine in question is CopyToPPT()
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try these lines immediately below On Error GoTo ErrHandler:

    objPPT.Visible = True
    Set objPres = objPPT.Presentations.Add(WithWindow:=True)

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Happy happy. Wow, that worked! I have no idea why though. I don't quite understand all the GetObject, CreateObject, Application.ActivateMicrosoftApp calls. They seem redundant but I'll try and read up on it. At first it was creating two PPT files, so I removed the Application.ActivateMicrosoftApp line and it creates one file.

    This was the last piece of this project to do and I thought I had it all working since I was only running it from debug as I was figuring out the details.

    Thank you so much.
    Deborah

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765647' date='16-Mar-2009 10:11']Try these lines immediately below On Error GoTo ErrHandler:

    objPPT.Visible = True
    Set objPres = objPPT.Presentations.Add(WithWindow:=True)[/quote]


    I spoke a bit too soon. It does all work great with adding this line, however it ignores the blank.pot file that I have. It doesn't use the default new PPT template and therefore the font style/colors, etc. aren't what I need. This isn't a show stopper but it'd be nice to have it use the same template that comes up with I manually create a new PPT file. This was how it worked before I added the code above. It seems weird that it doesn't use the default template.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='jujuraf' post='765660' date='16-Mar-2009 18:57']I spoke a bit too soon. It does all work great with adding this line, however it ignores the blank.pot file that I have. It doesn't use the default new PPT template and therefore the font style/colors, etc. aren't what I need. This isn't a show stopper but it'd be nice to have it use the same template that comes up with I manually create a new PPT file. This was how it worked before I added the code above. It seems weird that it doesn't use the default template.[/quote]
    You could use

    objPres.ApplyTemplate FileName:="...\blank.pot"

    where ... is the full path to the default template.

  8. #8
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765677' date='16-Mar-2009 12:52']You could use

    objPres.ApplyTemplate FileName:="...\blank.pot"

    where ... is the full path to the default template.[/quote]

    Ok thanks that makes sense, I'll try it. Since this Excel workbook will be used by many people and I don't know their user names, accurately getting the fulll path may not be practical. The folks I wrote this for are already super excited to have this time consuming process automated that having to manually copy these 12 slides to a new blank PPT (so the slides are in the right corporate template) isn't a big deal.

    I can get the user name in the Tools / Options / General tab. I also see the default path in Tools / Options / Save tab but haven't found the function to call to read that string. I'll keep looking through the object browser. I tried recording a macro where I changed those values but nothing got recorded.

    Thanks so much, this has been a great help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use Application.TemplatesPath & "\blank.pot"

    Application.TemplatesPath is the path to the user templates.

  10. #10
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765702' date='16-Mar-2009 15:15']You could use Application.TemplatesPath & "\blank.pot"

    Application.TemplatesPath is the path to the user templates.[/quote]

    I can't find TemplatesPath in the PP object library. I also looked on the Microsoft site, http://msdn.microsoft.com/en-us/library/aa...office.11).aspx and it's not listed there either. I only see readonly functions that return the current template name or one that let's you set it if you know the full path (which I tried and it does work but that won't work for any other user).

    Oh well, I'm happy with how it works now. The users can simply copy/paste these 12 slides to a new. One option is for me to do the code to open a new PPT (which does use the default .pot file) and copy those 12 slides into it.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Application.TemplatesPath is Excel VBA - you're doing this from Excel, aren't you?

    All Office applications share the same user templates path and workgroup templates path, so the path you get from Excel will be valid for PowerPoint too.

  12. #12
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='765715' date='16-Mar-2009 16:37']Application.TemplatesPath is Excel VBA - you're doing this from Excel, aren't you?

    All Office applications share the same user templates path and workgroup templates path, so the path you get from Excel will be valid for PowerPoint too.[/quote]


    Dang, I hate it when I do that. YES you're right, of course. I was looking at the PP object model and not from point of view of Excel. I was thinking they had different template paths. I have it all working perfectly now, this is soooo very great!!!

    Thank you for your patience.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •