Results 1 to 7 of 7

Thread: Export Excel Range To PPT As An Excel Table Not As Picture Using VBA Macro

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0

    Lightbulb Export Excel Range To PPT As An Excel Table Not As Picture Using VBA Macro

    Dear All,

    Need a help from you all.

    I've a sheet with lot of data on it and want to export six different ranges of excel to a PPT as a table ( each range in a slide) and not as a picture. I'm new to VBA and shall be grateful if some one could help me.



    Worksheet name - Status


    Ranges to be exported are

    B16:C19
    B23:H33
    B35:H36
    B67:V80
    B374:V390
    B1303:V1320

    Many thanks

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    So do you want these to be exported to a new slide? Or will you be selecting a slide of your choosing, and then have the ranges exported to existing slides, or new slides within that existing PPT file? If the latter, will the new slides start from the last existing slide?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0
    Thank you for your reply.

    The most preferred option is to start it in a new slide and if not the programme to indicate the user to open a ppt and keep it ready, before it executes the export.

    Many thanks again

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Here's this should help

    Code:
    Sub ExcelFox()
    
        Dim appPPT As Object 'PowerPoint.Application
        Dim pptNew As Object 'PowerPoint.Presentation
        Dim lyt As Object 'PowerPoint.CustomLayout
        Dim vararrRangeToExport As Variant, varEach As Variant
        
        vararrRangeToExport = Array("B16:C19", _
                                    "B23:H33", _
                                    "B35:H36", _
                                    "B67:V80", _
                                    "B374:V390", _
                                    "B1303:V1320")
        Set appPPT = CreateObject("PowerPoint.Application")
        appPPT.Visible = 1 'msoCTrue
        Set pptNew = appPPT.Presentations.Add(-1) 'msoTrue
        For Each varEach In vararrRangeToExport
            pptNew.Slides.Add Index:=pptNew.Slides.Count + 1, Layout:=12 'ppLayoutBlank
            Range(varEach).Copy
            Call pptNew.Slides(pptNew.Slides.Count).Shapes.PasteSpecial(10) 'ppPasteOLEObject
        Next varEach
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0
    Thank you very much for your kind help.

    Unfortunately I get a run time error '-2147417851 (8001010105)' ;

    Method 'PasteSpecial' of object 'Shapes's failed and the debug mode stops at the line

    ' Call pptNew.Slides(pptNew.Slides.Count).Shapes.PasteSpe cial(10) 'ppPasteOLEObject'

    the ppt also restarts even if I keep it open ( I've office 2007 version).

    Many thanks again

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Add the Microsoft PowerPoint 12.0 Library in the VBA project, and use this code

    Code:
    Sub ExcelFox()
    
        Dim appPPT As PowerPoint.Application
        Dim pptNew As PowerPoint.Presentation
        Dim vararrRangeToExport As Variant, varEach As Variant
        
        vararrRangeToExport = Array("B16:C19", _
                                    "B23:H33", _
                                    "B35:H36", _
                                    "B67:V80", _
                                    "B374:V390", _
                                    "B1303:V1320")
        On Error Resume Next
        Set appPPT = GetObject(, "PowerPoint.Application")
        Err.Clear: On Error GoTo 0: On Error GoTo -1
        If appPPT Is Nothing Then
            Set appPPT = CreateObject("PowerPoint.Application")
            appPPT.Visible = 1 'msoCTrue
        End If
        Set pptNew = appPPT.Presentations.Add(-1) 'msoTrue
        For Each varEach In vararrRangeToExport
            pptNew.Slides.Add Index:=pptNew.Slides.Count + 1, Layout:=12 'ppLayoutBlank
            Range(varEach).Copy
            Call pptNew.Slides(pptNew.Slides.Count).Shapes.PasteSpecial(10) 'ppPasteOLEObject
        Next varEach
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Jul 2013
    Posts
    6
    Rep Power
    0
    Hi Excel Fox- thank you for your kind time and help in this regard.


    I've already added the Microsoft PowerPoint 12.0 Library in the VBA project under Tools > References , and used this code and I'm afraid that I'm still getting the same error.

    Sorry.

Similar Threads

  1. Export data from Excel to Access Table (ADO) using VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-24-2015, 07:53 PM
  2. Excel 2013 - Picture Compression Macro
    By MCBrad in forum Excel Help
    Replies: 1
    Last Post: 07-11-2013, 07:24 PM
  3. Replies: 1
    Last Post: 07-10-2013, 11:38 AM
  4. Replies: 4
    Last Post: 07-02-2013, 11:32 AM
  5. Replies: 3
    Last Post: 03-05-2013, 03:57 PM

Posting Permissions

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