none
Create PowerPoint Slide for Each Row in Excel Workbook,It gives runtime error Integer value out of range is not in the valid range of 1 to 0 RRS feed

  • Question

  • Option Explicit
    
    Sub CreateSlides()
    Dim OWB As New Excel.Workbook
    Set OWB = Excel.Application.Workbooks.Open("D:\DD.xlsx") 'Change the file path here
    Dim WS As Excel.Worksheet
    Set WS = OWB.Worksheets(1)
    Dim str As String
    Dim i, j As Integer
    
    For i = 1 To WS.Range("A65536").End(xlup).Row
        ActivePresentation.Slides(1).Copy
        ActivePresentation.Slides.Paste (ActivePresentation.Slides.Count + 1)
        Dim LastCol As Long
        LastCol = WS.Rows(i).End(xlToRight).Column
        If LastCol = 16384 Then LastCol = 1
        str = ""
        For j = 1 To LastCol
            If j <> 1 Then str = str & Chr(13)
            str = str & WS.Cells(i, j).Value
        Next
        ActivePresentation.Slides(ActivePresentation.Slides.Count).Shapes(1).TextFrame.TextRange.Text = str
    Next
    End Sub

    Monday, August 12, 2019 10:46 AM

Answers

  • That is weird. I can reproduce that error message only if the active presentation is empty (i.e. it has no slides), but then the error occurs on the line

    ActivePresentation.Slides(1).Copy

    It runs flawlessly if the presentation has at least one slide...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by D_j_san Wednesday, August 14, 2019 10:23 AM
    Wednesday, August 14, 2019 9:38 AM

All replies

  • First of all, declare both the i and j as long like this...

    Dim i As Long, j As Long

    Which line causes the error you mentioned?


    Subodh Tiwari (Neeraj) sktneer

    Monday, August 12, 2019 12:24 PM
  • Does this work?

    Sub CreateSlides()
        Dim OXL As Excel.Application
        Dim f As Boolean
        Dim OWB As Excel.Workbook
        Dim WS As Excel.Worksheet
        Dim str As String
        Dim i As Long
        Dim j As Long
        Dim LastRow As Long
        Dim LastCol As Long
        Dim n As Long
        On Error Resume Next
        Set OXL = GetObject(Class:="Excel.Application")
        If OXL Is Nothing Then
            Set OXL = CreateObject(Class:="Excel.Application")
            f = True
        End If
        On Error GoTo 0
        n = ActivePresentation.Slides.Count
        Set OWB = OXL.Application.Workbooks.Open("D:\DD.xlsx") 'Change the file path here
        Set WS = OWB.Worksheets(1)
        LastRow = WS.Range("A" & WS.Rows.Count).End(xlup).Row
        For i = 1 To LastRow
            n = n + 1
            ActivePresentation.Slides(1).Copy
            ActivePresentation.Slides.Paste n
            LastCol = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
            str = ""
            For j = 1 To LastCol
                str = str & Chr(13) & WS.Cells(i, j).Value
            Next j
            ActivePresentation.Slides(n).Shapes(1).TextFrame.TextRange.Text = Mid(str, 2)
        Next i
        OWB.Close SaveChanges:=False
        If f Then
            OXL.Quit
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, August 12, 2019 2:52 PM
  • Hello Subodh,

    Thanks for reply,

    Can you help me this. I want any no  of rows and columns table to be exported to the Powerpoint having individual slides for the individual rows from the table.

    Again thanks for reply.. 

    Tuesday, August 13, 2019 9:37 AM
  • Did you test the code proposed by Hans?

    Btw you haven't answered to my query I posted in my first post yet i.e. which line causes the error?

    To get proper and to the point help, why not share the copy of ppt and excel files and mock up the desired output in ppt file for first 2 rows to show us the end result you are trying to achieve?


    Subodh Tiwari (Neeraj) sktneer

    Tuesday, August 13, 2019 11:28 AM
  • Hello Subodh,Hans

    Thanks For reply,

    Actually,

    After running Both code, one dialog box appears with a prompt message of "Run-time error-429"(ActiveX Component can't create object) .

    And For sending Copy of ppt and excel ,my account is not verify now.

    • Marked as answer by D_j_san Tuesday, August 13, 2019 12:21 PM
    • Unmarked as answer by D_j_san Tuesday, August 13, 2019 12:21 PM
    Tuesday, August 13, 2019 12:14 PM
  • I tested the Han's code and it worked without an issue on my end.

    Open a blank Excel workbook (not the file which is opened by the code) and then run the Han's code again, does that work for you?


    Subodh Tiwari (Neeraj) sktneer

    Tuesday, August 13, 2019 12:43 PM
  • If you click Debug in the error message, which line of the code is highlighted?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, August 13, 2019 1:00 PM
  • Hello Hans,

    Thanks for reply,

    Click to Debug The error message is on line " ActivePresentation.Slides.Paste n" .The code automatically stop this line and display error message "Slides(unknown member):integer out of range 1 is not in valid range of 1 to 0".I also include power Point Object Library.
    Wednesday, August 14, 2019 5:44 AM
  • That is weird. I can reproduce that error message only if the active presentation is empty (i.e. it has no slides), but then the error occurs on the line

    ActivePresentation.Slides(1).Copy

    It runs flawlessly if the presentation has at least one slide...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by D_j_san Wednesday, August 14, 2019 10:23 AM
    Wednesday, August 14, 2019 9:38 AM
  • Hello Hans,

    Thanks for your help.It is working now...

    Wednesday, August 14, 2019 10:25 AM