PDA

View Full Version : [SOLVED] When stepping through, code doesn't stop after this "paste" command...why?



ajjava
05-18-2019, 05:14 PM
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


2423424235

Artik
05-18-2019, 08:47 PM
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

p45cal
05-19-2019, 04:48 AM
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.

Paul_Hossler
05-19-2019, 06:01 AM
http://www.appspro.com/Utilities/CodeCleaner.htm

I found that cleaning the code often helps with strange behaviors

Artik
05-19-2019, 07:49 AM
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/showthread.php?65122-VBA-to-copy-CHECKED-picture-objects-into-EXISTING-Powerpoint&p=390774&viewfull=1#post390774

Artik

ajjava
05-19-2019, 11:13 AM
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 :)

ajjava
05-19-2019, 11:19 AM
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/showthread.php?65122-VBA-to-copy-CHECKED-picture-objects-into-EXISTING-Powerpoint&p=390774&viewfull=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:bow::bow::clap:

Artik
05-19-2019, 02:27 PM
THANK YOU:bow::bow::clap:

Trifle, two hundred bucks. :winking2:

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

ajjava
05-19-2019, 02:41 PM
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 :)

Artik
05-20-2019, 03:13 AM
Testing in my office will be the true testAnd I am waiting for the results of this test. :)



and, one of the suggested fixes is the DoEvents line that you also includedMy 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

ajjava
05-20-2019, 06:26 AM
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:
24240

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?

ajjava
05-20-2019, 07:54 AM
24243And 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:
24244

ajjava
05-22-2019, 06:41 AM
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.

Artik
05-22-2019, 08:17 PM
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.


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. :winking2: But figuring out how the code is supposed to work, it's been taking forever. :creator:


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. :super:



Artik

ajjava
05-23-2019, 06:28 AM
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 :)

Artik
05-23-2019, 12:40 PM
Wait a moment.
Line 1217 is a comment. Did you uncomment her?

Artik

ajjava
05-24-2019, 07:32 AM
I didn't uncomment the line. Weird, right?

Artik
05-24-2019, 02:00 PM
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

ajjava
05-24-2019, 02:12 PM
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.

Artik
05-24-2019, 02:30 PM
I'll be waiting with longing. :)

Artik

ajjava
05-28-2019, 12:06 PM
Hello, my friend. So, I tried your suggestion. The code failed after copy/pasting to the first slide. This time, the error was at line 1224. Another curious thing I found, a thing that may be causing me problems: Look at the second picture I've included here. I have to manually switch the Powerpoint object library back to 14.0, because 16.0 is missing. Could that be an issue?

24288
24289

Artik
05-29-2019, 03:21 PM
Another curious thing I found, a thing that may be causing me problems: Look at the second picture I've included here. I have to manually switch the Powerpoint object library back to 14.0, because 16.0 is missing. Could that be an issue? MS Office is doing well with the change references from lower to higher version. But vice versa - he has a problem because he can not do it properly. Yes, you must manually change the reference at 14.0, or... apply late binding, as in the new proposal.

This time, the error was at line 1224.What plagues await me yet? :) You have received this error after the change of reference?

See if the new version will be better.

Artik

ajjava
06-03-2019, 08:15 AM
Well, my friend - more adventures. This time, the macro caused PowerPoint to crash entirely. I'm certain your code is efficient and accurate - it's this horrible network that I'm on here at work that's causing the issues. When I tried to run it again, I got this message:
24337

As long as I close out of all other applications and turn off the error handling snippets that you included, I can use your code without issue. It does exactly what I'd hoped it would, and now I'm tweaking things here and there, based on what mgmt wants. All in all, you've gone above and beyond anything I expected and I thank you for that. Your script will have a HUGE positive impact on the efficiency of my entire group.:clap::bow::hi:

Artik
06-03-2019, 01:12 PM
I tried as best I could, but my ideas already exhausted. I give up. :crying:

Can I ask for some other set of questions? :biglaugh: :winking2:

Artik

ajjava
06-04-2019, 08:43 AM
"...some other set of questions" as it relates to this macro? Or would you like a whole new challenge? :yes