Results 1 to 2 of 2

Thread: Alter pastelink paths

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Junior Member
    Join Date
    Nov 2019
    Posts
    2
    Rep Power
    0

    Alter pastelink paths

    Hello
    In powerpoint I will have the first slide as a slide to select what data to display on all the other slides (I'll refer to this slide as "Main")
    Scenario:
    I have an excel workbook with 51 sheets - "Group 1" to Group 50" and a sheet to link a range of cells to my powerpoint slides (Range A3:A22) --"Linked Words" Sheet

    This will be an slideshow for flash words for Grade 4 students.
    The Main slide will have either 50 ctrl boxes or links (in text boxes) (not sure which I can use)

    Because potentially, each time this ppt is used, it could be run from either a thumb drive or a path on the HD, I need to either Prompt the user for the path to the excel file (preferrably a windows browse box) or Grab the path from where the ppt file was opened (I'm going to assume the ppt file and xl file are both in the same place).
    Then, the end user would click one of the 50 boxes on the Main slide. Doing this would copy the words from the appropriate Group (in excel - sheet, range a3:a22) to the "Linked Words" sheet in Excel.

    Example:
    End user opens the ppt presentation. Gets prompted to the path to the Excel file (should always be in the same place as the ppt file) and browses to the place of the excel file (then the "Pastelink" paths are updated in powerpoint).
    End user clicks "Group 25" box (or button) on the Main slide. (Could be any of "Group 1" to "Group 50")
    Files are copied from the Excel Sheet "Group 25" (range a3:a22) to the "Linked Words" Excel sheet (range a3:a22).

    This way, the rest of the powerpoint slides auto display the changes to that range on the Excel Sheet "Linked Words". (Each of the slides will have a "Pastespecial/Pastelink" value on it with annimations etc (appear/disappear).

    The only other way I can see to set this up is to create 50 ppt files, each linking to each of the 50 workbook sheets and cell.

    I am more familiar with powershell, excel vba and have never tried ppt vba before - and I'm on a bit of a crunch for time.
    Because this will be run from a computer across the room, doing the copy paste manually in excel is not an option. It has to be done from powerpoint.

    Any help would be much appreciated.

    Thank you
    Terry

  2. #2
    Junior Member
    Join Date
    Nov 2019
    Posts
    2
    Rep Power
    0
    I created a command button on a slide. Attached this code:
    All works fine until I get to the pastespecial part.

    Code:
    Private Sub CommandButton1_Click()
    Dim xlApp As Object
    Dim curpth
    Set xlApp = CreateObject("Excel.Application")
    curpth = Application.ActivePresentation.Path
    'bookpth = Application.ActiveWorkbook.FullName
    xlApp.Visible = True
    
    xlApp.Workbooks.Open curpth & "\Frye Words Excel Sheet.xlsx", True, False
    
    xlApp.Worksheets("Group 1-1").Range("A3:A23").Copy
    xlApp.Worksheets("LinkData").Activate
    xlApp.Worksheets("LinkData").Range("A3").PasteSpecial Paste:=xlpastevalues  'This line fails  Error 1004 Pastespecial method failed.
    'Application.cutcopymode = False
    
    Set xlApp = Nothing
    End Sub
    This is as far as I've gotten.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-20-2023 at 03:53 PM.

Similar Threads

  1. Built in VBA methods and functions to alter the contents of existing character strings
    By DocAElstein in forum Familiar with Commands and Formulas
    Replies: 1
    Last Post: 02-14-2018, 03:09 AM

Posting Permissions

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