Copy every three cells from Excel to ppt

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Copy every three cells from Excel to ppt

Post by gailb »

In column A there are sentences. I need to move the sentences to a ppt. This code below works sporadically. One time it will loop thru 3 times and then error, and maybe the next time, 7 times an error. No matter how many times the procedure is run, it never finishes and always debugs to

Code: Select all

.Shapes(2).TextFrame.TextRange.PasteSpecial DataType:=9

Code: Select all

    x = Range("I1").Value
    For i = 1 To x Step 3
        'Copy Range from Excel
        Set Rng = ThisWorkbook.ActiveSheet.Cells(i, 1).Resize(3)
        Rng.Copy

        SlideCount = PPPres.Slides.Count
        Set PPSlide = PPPres.Slides.AddSlide(SlideCount + 1, PPTLayout)
        PPApp.ActiveWindow.View.GotoSlide PPSlide.SlideIndex

        With PPSlide
            .Shapes(1).TextFrame.TextRange = Range("H1").Value
            .Shapes(2).TextFrame.TextRange.PasteSpecial DataType:=9
        End With
    Next i
Capture.JPG
You do not have the required permissions to view the files attached to this post.

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Copy every three cells from Excel to ppt

Post by gailb »

It seems this solution has worked. I added a pause right after the rng.copy

Code: Select all

Application.Wait (Now + TimeValue("0:00:1"))
Is it possible the code was outrunning the clipboard??? :blowup:

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy every three cells from Excel to ppt

Post by HansV »

Probably. You can also try inserting one or two lines

DoEvents

instead of Application.Wait
Best wishes,
Hans

gailb
3StarLounger
Posts: 254
Joined: 09 May 2020, 14:00

Re: Copy every three cells from Excel to ppt

Post by gailb »

Hi Hans and thanks. I replaced the Application.Wait with the DoEvents and in runs extremely fast and without causing an error. After reading up on the DoEvents, I can't say I completely understand it, but it works. Thank you.

User avatar
HansV
Administrator
Posts: 78231
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy every three cells from Excel to ppt

Post by HansV »

DoEvents gives the operating system a bit of breathing space between operations, so that it can catch up.
Best wishes,
Hans