Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 25

Thread: When stepping through, code doesn't stop after this "paste" command...why?

  1. #1
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location

    When stepping through, code doesn't stop after this "paste" command...why?

    The code posted here worked fine...until I added a For loop to go through all sheets.

    I have an Excel workbook with multiple sheets, each sheet containing multiple picture objects, with corresponding data ranges. The code is meant to:

    * For each sheet
    * Copy first picture object
    * Create Powerpoint presentation, insert slide
    * Paste the picture object onto slide
    * Go back to the Excel sheet
    * Copy the data range that is directly below the picture object that was just copied
    * Go back to the SAME slide in Powerpoint
    * Paste the data range (AS A PICTURE) beneath the already-pasted picture object
    * Repeat for all picture/data range combos on a sheet (creating a new PPT slide for every combo)
    * Repeat for all sheets in workbook

    The code ALMOST works. The problem is that it just keeps going through all of the picture/data ranges on the FIRST SHEET. It never progresses to the next sheet. HOWEVER, it is also executing the copy/paste of all those pic/range combos MULTIPLE times in Powerpoint (i.e. I should have the same number of slides as I have number of pic/range combos...but instead, I end up with THREE TIMES the number of slides, because it just keeps looping).

    So, I'm trying to step through, line by line, to identify the issue (I did post this same script here yesterday, but with no luck...so I'm trying to troubleshoot on my own).

    For some reason, when the script gets to this line...

    PPApp.ActiveWindow.View.Paste

    ...the stepping stops and the code just runs all the way through (albeit incorrectly). I've never experienced that before with stepping.

    Why is it doing that? Any assistance at all on this would be SOOO appreciated.

    NOTE: The workbook attached here is pared down in order to meet the forum size limit. The actual workbook will have about 25 sheets.
    Sub CPAT_ExcelToPowerPoint() '*****  THIS IS THE CORRECT CODE TO USE FOR COPYING CPAT FROM EXCEL TO POWERPOINT   *************************
    
    
    
        
        
        
    'Declare variables
            
    Dim PPApp As PowerPoint.Application
    Dim PPPres As PowerPoint.Presentation
    Dim PPSlide As PowerPoint.Slide
    Dim SlideCount As Integer
    Dim myShp As Shape
    Dim slTitle As String
    Dim mysht As Worksheet
    
    
    
    
    'Start a new instance of Powerpoint
    Set PPApp = New PowerPoint.Application
    PPApp.Visible = True
    
    
    
    
    
    
    
    
    'Create new ppt
    Set PPPres = PPApp.Presentations.Add
        
    
    
    For Each mysht In ActiveWorkbook.Worksheets
            
            If mysht.Name <> "Definition and Filter" And _
             mysht.Name <> "Performance Summary" And _
             mysht.Name <> "Perf Summary no Charts" Then
            
            
    
    
    
    
            'Make sure the correct starting cell is selected in Excel, so that the 'CurrentRegion' selection will work
            Range("A2").Select
            
                    
                'Loop through all the pictures on the sheet. Select picture, copy it
                For Each Shape In ActiveSheet.Shapes
                   
                   If Left(Shape.Name, 7) = "Picture" Then
                       
                        Shape.CopyPicture Appearance:=xlScreen, Format:=xlPicture
                    End If
                    
                    DoEvents   'This line is added so that Excel has time to complete the copy/paste operation
                  
                  
    '              Create New Slide
                  Set PPSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, ppLayoutTitleOnly)
                  PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex    ' activate the slide
                  
                  'Setting the slide title variable, based on the worksheet name
                  slTitle = ActiveSheet.Name
                  
                  'Paste the picture in the newly created slide
                  PPApp.ActiveWindow.View.Paste
                  DoEvents
                   
                  'Select first region of data
                  ActiveCell.CurrentRegion.Select
                  Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
                  DoEvents
                  
                  'Paste the data in the newly created slide
                  PPApp.ActiveWindow.View.Paste
                  DoEvents
                  
                  'Select next region of data
                  Selection.End(xlToRight).Select
                  Selection.End(xlToRight).Select
                  
               
                  'Add the title to the slide
                  PPSlide.Shapes.Title.TextFrame.TextRange.Text = slTitle
                    
                Next Shape
    End If
    Next mysht
    
    
    
    
    
    
    
    
    Set PPSlide = Nothing
    Set PPPres = Nothing
    Set PPApp = Nothing
        
         
    
    End Sub

    aaClaim_Performance_Analytic_Tool_-_Combined_-_WC_FORMATTING_APRIL_2019 PARED.xlsxCPAT NEW FORMAT TEST.pptx

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    You forgot to activate each of the sheets.
    But basically, you do not have to activate them. You do not need to select cells too. :-)


    Note how I designate the scope of the table to be copied. I download the cell that contains the top left corner of the image, then move it one row down and one column to the right. and then I designate CurrentRegion. This shift to the right is only a safeguard in case the image goes left beyond the edge of the table.
    Sub CPAT_ExcelToPowerPoint_1()    '*****  THIS IS THE CORRECT CODE TO USE FOR COPYING CPAT FROM EXCEL TO POWERPOINT   *************************
        
        'Declare variables
    
    
        Dim PPApp       As PowerPoint.Application
        Dim PPPres      As PowerPoint.Presentation
        Dim PPSlide     As PowerPoint.Slide
        Dim SlideCount  As Integer
        Dim myShp       As Excel.Shape
        Dim slTitle     As String
        Dim mySht       As Excel.Worksheet
        Dim Rng         As Excel.Range
    
    
        'Start a new instance of Powerpoint
        Set PPApp = New PowerPoint.Application
        PPApp.Visible = True
    
    
    
    
        'Create new ppt
        Set PPPres = PPApp.Presentations.Add
    
    
        For Each mySht In ActiveWorkbook.Worksheets
    
    
            If mySht.Name <> "Definition and Filter" And _
               mySht.Name <> "Performance Summary" And _
               mySht.Name <> "Perf Summary no Charts" Then
    
    
                'mysht.Activate
    
    
                'Loop through all the pictures on the sheet. Select picture, copy it
                For Each myShp In mySht.Shapes
    
    
                    If LCase(myShp.Name) Like "picture*" Then
                        
    
    
                        myShp.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    
                        'DoEvents   'This line is added so that Excel has time to complete the copy/paste operation
    
    
                        'Create New Slide
                        Set PPSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, ppLayoutTitleOnly)
                        PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex    ' activate the slide
    
    
                        'Setting the slide title variable, based on the worksheet name
                        slTitle = mySht.Name
    
    
                        'Paste the picture in the newly created slide
                        PPApp.ActiveWindow.View.Paste
                        'DoEvents
    
    
                        'Select first region of data
                        Set Rng = myShp.TopLeftCell.Offset(1, 1).CurrentRegion
                        'ActiveCell.CurrentRegion.Select
                        Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
                        'DoEvents
    
    
                        'Paste the data in the newly created slide
                        PPApp.ActiveWindow.View.Paste
                        'DoEvents
    
    
                        'Add the title to the slide
                        PPSlide.Shapes.Title.TextFrame.TextRange.Text = slTitle
                        
                        'DoEvents
                    End If
    
    
                Next myShp
    
    
            End If
    
    
        Next mySht
    
    
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing
    
    
    End Sub
    The second version of the macro copies the tables with the image and, as a whole, pastes it into the PP. Before copying, all images are centered relative to their tables.
    Sub CPAT_ExcelToPowerPoint_2()
    
    
        Dim PPApp       As PowerPoint.Application
        Dim PPPres      As PowerPoint.Presentation
        Dim PPSlide     As PowerPoint.Slide
        Dim SlideCount  As Integer
        Dim myShp       As Excel.Shape
        Dim slTitle     As String
        Dim mySht       As Excel.Worksheet
        Dim Rng         As Excel.Range
    
    
        Call CenteringCharts
    
    
        Set PPApp = New PowerPoint.Application
        PPApp.Visible = True
    
    
        Set PPPres = PPApp.Presentations.Add
    
    
        For Each mySht In ActiveWorkbook.Worksheets
    
    
            If mySht.Name <> "Definition and Filter" And _
               mySht.Name <> "Performance Summary" And _
               mySht.Name <> "Perf Summary no Charts" Then
    
    
                For Each myShp In mySht.Shapes
    
    
                    If LCase(myShp.Name) Like "picture*" Then
    
    
                        Set PPSlide = PPPres.Slides.Add(PPPres.Slides.Count + 1, ppLayoutTitleOnly)
                        PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex
    
    
                        slTitle = mySht.Name
    
    
                        Set Rng = myShp.TopLeftCell.CurrentRegion
    
    
                        'Copy Picture with its table
                        Rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    
    
                        PPApp.ActiveWindow.View.Paste
                        PPSlide.Shapes.Title.TextFrame.TextRange.Text = slTitle
    
    
                    End If
    
    
                Next myShp
    
    
            End If
    
    
        Next mySht
    
    
        Set PPSlide = Nothing
        Set PPPres = Nothing
        Set PPApp = Nothing
    
    End Sub
    
    
    Sub CenteringCharts()
        Dim wks         As Worksheet
        Dim shp         As Shape
        Dim Lft         As Single
        Dim Wdth        As Single
        Dim Rng         As Range
    
    
        For Each wks In ActiveWorkbook.Worksheets
            For Each shp In wks.Shapes
                If LCase(shp.Name) Like "picture*" Then
                    Set Rng = shp.TopLeftCell.Offset(, 1).CurrentRegion
                    Wdth = Rng.Width
                    With Rng
                        Lft = .Left + (.Width - shp.Width) / 2
                    End With
    
    
                    shp.Left = Lft
                End If
            Next shp
        Next wks
    End Sub
    
    
    Sub AlignToLeftCharts()
        Dim wks         As Worksheet
        Dim shp         As Shape
        Dim Rng         As Range
    
    
        For Each wks In ActiveWorkbook.Worksheets
            For Each shp In wks.Shapes
                If LCase(shp.Name) Like "picture*" Then
                    Set Rng = shp.TopLeftCell.Offset(, 1).CurrentRegion
    
    
                    shp.Left = Rng.Left
                End If
            Next shp
        Next wks
    
    End Sub
    Artik

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by ajjava View Post
    So, I'm trying to step through, line by line, to identify the issue (I did post this same script here yesterday, but with no luck...so I'm trying to troubleshoot on my own).

    For some reason, when the script gets to this line...
    [B]
    PPApp.ActiveWindow.View.Paste

    ...the stepping stops and the code just runs all the way through (albeit incorrectly). I've never experienced that before with stepping.

    Why is it doing that? Any assistance at all on this would be SOOO appreciated.
    I don't know why it's doing that but it happens to me from time to time. You can either add a breakpoint or two (F9 in the vbe when the cursor is on the line you want it to stop before) or you could add a temporary Stop line.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    http://www.appspro.com/Utilities/CodeCleaner.htm

    I found that cleaning the code often helps with strange behaviors
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Perhaps I already know why it is stopped while the code is executing.
    It is likely that the source book is activated when you copy the range in the worksheet. And the written code predicts that the PP application is always active.
    After copying the range, you should activate PP, give a moment of rest (DoEvents) and we can go further.

    I showed the solution in another thread: http://www.vbaexpress.com/forum/show...l=1#post390774

    Artik
    Last edited by Artik; 05-19-2019 at 08:13 AM.

  6. #6
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    Thanks to all of you for your help. I'm going to test the code when I'm back in the office, tomorrow.

    Yes, I know my original code is far from clean. I'm still quite new to in-depth VBA and am very literal with my commands. This often results in crazy, tough-to-diagnose results.

    For every solution that I get here on the forum, my experience and knowledge grows. It's invaluable and I can't thank all of you VBA experts enough

  7. #7
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    Quote Originally Posted by Artik View Post
    Perhaps I already know why it is stopped while the code is executing.
    It is likely that the source book is activated when you copy the range in the worksheet. And the written code predicts that the PP application is always active.
    After copying the range, you should activate PP, give a moment of rest (DoEvents) and we can go further.

    I showed the solution in another thread: http://www.vbaexpress.com/forum/show...l=1#post390774
    Artik
    HOLY COW!!! That other thread is also mine, and is for this same project (but a few iterations ago). Your solution is utterly AMAZING!!!! This is worlds beyond what I had hoped for. THANK YOU THANK YOU THANK YOU

  8. #8
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by ajjava View Post
    THANK YOU
    Trifle, two hundred bucks.

    ajjava, I have a request to tell me after the tests if the bugs stopped appearing (unexpected stopped code execution). It is important for me.

    Artik

  9. #9
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    I was able to test it from home and so far, it is 100% perfectly outstanding

    Testing in my office will be the true test, since I was also having real performance issues there. It won't be a reflection of your script, though, if issues do arise. For some reason, PowerPoint was sporadically but regularly crashing, any time an attempt was made to copy/paste between it and Excel. From what I read, this is a somewhat common "bug" (and, one of the suggested fixes is the DoEvents line that you also included).
    I will absolutely report back tomorrow morning, after I run my tests. All I know is that my bosses were counting on me and now, you've helped me to be the hero

  10. #10
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by ajjava View Post
    Testing in my office will be the true test
    And I am waiting for the results of this test.


    Quote Originally Posted by ajjava View Post
    and, one of the suggested fixes is the DoEvents line that you also included
    My tests show that it is very important to activate PP every time before pasting the scope from Excel. DoEvents itself is insufficient. I can only guess that when copying the scope from Excel, the workbook is activated and sometimes there is no automatic return to activate the PP.

    Artik

  11. #11
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    Ok, so...as I suspected, there is some kind of issue on my end that is causing a glitch between Excel and Powerpoint, when attempting to copy/paste multiple items in one operation. I can then step into the code and hit the Run Sub button, at the point of the failure, and the routine finishes without issue 98% of the time.
    The other 2%, I get this error here:
    error on copy paste CPAT.JPG

    I am unable to make the routine continue after receiving this error and must run the whole thing over again. It may be worth noting that the actual file that this macro will be run on has many more sheets than what I posted in my sample. I had to pare it down, in order to meet the forum size requirement. Could that be the issue? I'm asking too much of computer, here at work?

  12. #12
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    3-Year Test CPAT v.2 modified.xlsxAnd one more item for adjustment (if you'd be so kind) - in some cases, we are only charting 3 years of data (rather than 5 years, as tested on here). In those cases, because the chart picture object is larger than the data table, the CurrentRegion selection is misbehaving. Two chart/table combos are being pasted onto one slide. Is there an adjustment for that?

    Here is the resulting slide:
    error on copy paste CPAT3.JPG

  13. #13
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    Artik - I'm hoping and wishing as hard as I can that you'll return to this post and see my latest request for assistance/modification. I'm SOOOO close now.

  14. #14
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Quote Originally Posted by ajjava View Post
    in some cases, we are only charting 3 years of data (rather than 5 years, as tested on here).
    This makes that the whole concept had to change.

    Quote Originally Posted by ajjava View Post
    I'm hoping (...) that you'll return to this post and see my latest request for assistance/modification.
    Writing the code does not last as long as its execution. It lasts definitely shorter. But figuring out how the code is supposed to work, it's been taking forever.


    I spent many hours on tests and I think I finally discovered why the code execution sometimes stops. The code works too fast, so sometimes it will not be able to copy the range from the source (and the clipboard is empty). And when the clipboard is empty, you can not paste anything. These two places in the code cause errors.


    The new version should cope with errors. In two sensitive procedures, I put error handling. I also changed the code's operation philosophy. In one combo I create two images (separately chart and table), center them in relation to each other, and then group them and create a new image from the group. This all happens when the "Please wait" text is displayed.


    Let me know if everything works without errors.


    I think that now you will not be a hero, but Superman.



    Artik
    Attached Files Attached Files

  15. #15
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    As I expected, you solved my main problem (3yr vs 5yr). I did still experience failures during execution, at various points in the process. The error handling message told me it was on line 1217 of the code. In order to be able to "restart" the procedure (by hitting F5), I deactivated the error handling. By doing that, I was able to by-pass the failure point and the code continued to completion.
    My team is going to be so happy just to have this script that they won't mind having to deal with a bug or two (and again, I have to think the issue is on my end, due to our antiquated systems/network).
    Clearly, you've spent quite some time creating/troubleshooting this series of procedures- I can't express how grateful I am for that. Dziękuję Dziękuję Dziękuję....from this half-Polish girl

  16. #16
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Wait a moment.
    Line 1217 is a comment. Did you uncomment her?

    Artik

  17. #17
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    I didn't uncomment the line. Weird, right?

  18. #18
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    Well, strange.
    For sure it was a bug in line 1217 and not 1216? It is important for me.
    I live a little longer on this world, but the VBA does not cease to suprise me.
    Delete entire lines 1217 and 1220, or only numbering of these lines. In the event of a runtime error on line 1216 or 1222, execution should proceed to error handling (Select Case Erl (); Case 1216 or Case 1222). If an error occurs 50 times in a row, the macro will be interrupted with an appropriate message that the scope can not be copied or pasted into the slide. In the event of an error in a line other than 1216 and 1220, you will receive a message with a system's description of the error and the line of code in which the error occurred.

    Artik

  19. #19
    VBAX Regular
    Joined
    Mar 2019
    Posts
    73
    Location
    I will try it again after the long weekend. I promise not to leave you in suspense forever �� The message box that popped up on the error indicated line 1217, for sure. Stay tuned - more testing to come.

  20. #20
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    I'll be waiting with longing.

    Artik

Posting Permissions

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