+ Reply to Thread
Results 1 to 2 of 2

VBA code to copy excel tables and paste into Power Point

  1. #1
    Registered User
    Join Date
    06-09-2017
    Location
    São Paulo
    MS-Off Ver
    2016
    Posts
    1

    VBA code to copy excel tables and paste into Power Point

    Good night to you all.

    In advance, I excuse for my English, it's not my native language.

    Anyway, I'm trying to automate one of the tasks I perform in my job. To do so, I'm trying to fetch a code that copies 25 tables from 4 different Excel sheets (same workbook) and paste them into Power Point. I've come across this article in Spreadsheet Guru that almost solves my problem:

    https://www.thespreadsheetguru.com/b...erpoint%20open

    In the website's example, the author creates an array to store the tables and another array with the PPT slides in which the tables will be copied to. However, his arrays have the same sizes, since he copies one single table to one single slide. So, he describes his arrays like this:

    MySlideArray = Array(2, 3, 4, 5, 6)

    MyRangeArray = Array(Sheet1.Range("A1:C10"), Sheet4.Range("A1:C10"), _
    Sheet3.Range("A1:C10"), Sheet2.Range("A1:C10"), Sheet5.Range("A1:C10"))


    My problem is: I have 25 tables and I want to copy them in 9 different slides (more than one table per slide), so my table array is bigger than my slide array

    To do the copy and paste, the author uses a loop with this code:

    'Loop through Array data
    For x = LBound(MySlideArray) To UBound(MySlideArray)
    'Copy Excel Range
    MyRangeArray(x).Copy

    'Paste to PowerPoint and position
    On Error Resume Next
    Set shp = myPresentation.Slides(MySlideArray(x)).Shapes.PasteSpecial(DataType:=2) 'Excel 2007-2010
    Set shp = PowerPointApp.ActiveWindow.Selection.ShapeRange 'Excel 2013
    On Error GoTo 0


    Since I want to copy more than one table in one slide, how can I write my code? That author's logic is completely clear to me, but I can't find a way to modify it to my needs. How do I assign my tables to the respective slides?

    Thank you a lot in advance!

  2. #2
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: VBA code to copy excel tables and paste into Power Point

    If you make MySlideArray a string array each entry can contain a delimited range of table numbers as a text string. When you loop through each entry in MySlideArray you can then split that entry into it's respective numbers and then loop through those numbers

    Please Login or Register  to view this content.
    In your example code both MyRangeArray & MySlideArray are iterated through in the same loop therefore they index to the same number; in this version because you have a nested loop they'll have different index numbers i.e. MyRangeArray(TableNumber) and MySlideArray(x)
    Last edited by thatandyward; 06-09-2017 at 07:58 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy - paste tables to Power Point
    By Link in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2014, 02:14 PM
  2. Pasting tables from excel to power point - position
    By ramaya in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2013, 05:00 PM
  3. power point VBA , tables
    By ziadAlsayed in forum Excel General
    Replies: 1
    Last Post: 01-23-2013, 05:34 AM
  4. Paste unlinked Charts from Excel to Power point using VBA
    By manucho9 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-22-2012, 02:33 PM
  5. How to paste Excel as linked object in Power Point
    By floricita in forum Excel General
    Replies: 1
    Last Post: 10-31-2011, 01:26 PM
  6. copy data from excel to power point
    By iscar_marius in forum Excel General
    Replies: 8
    Last Post: 02-09-2009, 08:20 AM
  7. Replies: 8
    Last Post: 11-21-2005, 03:50 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1