Results 1 to 22 of 22

Thread: Import data from excel to a powerpoint label

  1. #1

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Import data from excel to a powerpoint label

    I have this excel file that works as a data base, there i fill everyday 5 tables in 5 different worksheets, each table grows along with the day, so each row is linked to an specific date.

    Then i need to make a daily report in a powerpoint presentation. I've done the presentation as a template, putting images of the tables and then embedding labels in everyplace i need.

    Now the request, I need to do a vba program that take the value of one cell that is in the same row as some specific date (that i can define) and then put it in a label on a existing powerpoint presentation. After that i need that the these changes will be saved in a new file with the date as file name.
    I hope i explained myself clear enough because I'm not native English speaker.

    I'm using office 2003. All the help will be appreciated

    Thanks in advance!

  2. #2
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    Say a bit more. For example, what do you want to "trigger" the action? Clicking a button, and get the value based on selected cell/row?

  3. #3

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    I would like to have a macro that when starts asks for the date that i want the report to linked to, then check it with the data within the excel file and after that send it to the respective powerpoint label.

    As a start it would be helpeful some code that can take one value from excel and then put it in a label embedded label on a powerppoint presentation. I think I can manage the rest, while i'm learning VBA.

    Thanks again.

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    I have a worksheet with dates in column A, starting in row 2, and some "values" in column B. This code will ask for a date to be input, find it in column A, get its associated value from column B, then open a pre-existing powerpoint presentation and place the value into a textbox in the first slide:

    Code:
    Private Sub cmdExport_Click()
        Dim resp As Date
        Dim ws As Worksheet
        Dim lr As Long
        Dim j As Long
        Dim val As String
        
        Dim ppt As PowerPoint.Application
        Dim pres As PowerPoint.Presentation
        Dim slide As PowerPoint.slide
        Dim tb As PowerPoint.Shape
           
        Set ws = ActiveSheet
        lr = ws.Range("a" & Rows.Count).End(xlUp).Row   'last row of data in column A
        
        resp = InputBox("Enter date in m/dd/yy format") 'input the date
        
        For j = 2 To lr
            If ws.Range("a" & j).Value = resp Then  'found matching date in column A
                val = ws.Range("b" & j).Value       'get value from same row, column B
                Exit For
            End If
        Next j
        
        Set ppt = CreateObject("PowerPoint.Application")
        ppt.Visible = True
        
        If val <> "" Then
            Dim strPath As String
            strPath = "c:\yourPath\pp1.pptx"        'change this
            Set pres = ppt.Presentations.Open(strPath)
            Set slide = pres.Slides(1)      'first slide
            Set tb = slide.Shapes("TextBox 1")
            tb.TextFrame2.TextRange.Characters.Text = val   'put "val" into textbox 1
        End If
        'save...close...etc.
    End Sub

  5. #5

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    Thanks for your answer...

    I read it all and I rhink that helps a lot, but when i run it stops highlighting in gray "ppt As PowerPoint.Application" and showing this message (translated from spanish) "Compilation error" & "Has not been define the define type by the user".

    Should be something simple that i ignore...

    Hope that you can help me.

    Regards

  6. #6
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    my fault. you have to add a reference to Microsoft Powerpoint (from the Tools...References menu).

  7. #7

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    I found the solution and didn't notice your answer, thanks anyway.

    Now it's stopping again, it highlights in gray the word "TextFrame2" (from the line "tb.TextFrame2.TextRange.Characters.Text = val") an shows a message box saying "Compilation error:" & "has not been found the method or the member data".

    I hope that third time's the charm...

  8. #8
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    If you go to the part of the code after "tb" has been set and start typing this:

    "tb.textFrame2..." etc, do you get the Intellisense popping up with the options to finish the line as I showed?

  9. #9
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    If you go to the part of the code after "tb" has been set and start typing this:

    "tb.textFrame2..." etc, do you get the Intellisense popping up with the options to finish the line as I showed?

  10. #10

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    Ok, I checked the intellisense and then the only thing that worked was to supress the number 2 after "tb.textFrame2".

    After that, I ran it again and then came a message box saying "runtime error: -2147188160 (80048240)" & "textframe unknown member: invalid request. This type of shape cannot have a TextRange".

    I tried adding the number 2 again but still the same problem as in earlier posts.

    We're almost there... Thanks for the help

  11. #11
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    I'm wondering if the properties were different in 2003. Based on this link:

    http://www.vbforums.com/showthread.p...Box-Validation

    they may be.

    Try something like:

    Code:
    ...Slide1.Textbox1.Value...
    Last edited by vbfbryce; Mar 19th, 2014 at 03:23 PM.

  12. #12
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Import data from excel to a powerpoint label

    Quote Originally Posted by vbfbryce View Post
    I'm wondering if the properties were different in 2003. Based on this link:

    http://www.vbforums.com/showthread.p...Box-Validation

    they may be.

    Try something like:

    Code:
    ...Slide1.Textbox1.Value...
    2003 did not have the TextFrame2 property. Based on OP #10 post, I think you need to determine what type of shape you are dealing with.

    i.e. Debug.Print tb.Type

    It does does not sound like you got a msoTextBox (value=17). If it was, you could use something like this:
    Code:
    tb.TextFrame.TextRange.Text="something"
    Office 2003 Editions: PowerPoint VBA Language Reference

  13. #13

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    Hi!

    I tried both "...Slide1.Textbox1.Value..." and "tb.TextFrame.TextRange.Text="something"" but neither one have succeed. I just downloaded the Office 2003 Editions: PowerPoint VBA Language Reference and i'll try to see if I can get something from it.

    I also checked that the code can actually can manipulate the "textbox1", modifying height and widht, among others, but didn't find the way to change the text.

    Regards

  14. #14
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Import data from excel to a powerpoint label

    Is there the possibility of you uploading as copy template so that we could take a look at it?

  15. #15

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    I tried to upload the file but i can't, some pop up message says invalid type... What do I have to know to upload the files?

    I found this line that change at least the textbox of title of the slide

    Code:
    "slide.Shapes.Title.TextFrame.TextRange.Text = val"
    Regards

  16. #16
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    Zip to upload

  17. #17

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Re: Import data from excel to a powerpoint label

    Hi!

    There are two files, the first "Presentacion.ppt" is the one the i'm testing with... the second one "Propuesta INFORME GENERAL PRODUCCION 3" is the one the i really need to work with, it has 148 labels that ought to be change through the code.

    Regards
    Attached Files Attached Files

  18. #18
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    The shape on the sample presentation slide is "type 12." I'm not sure what that constant refers to.

    Tin, do you know?

  19. #19
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Import data from excel to a powerpoint label

    Quote Originally Posted by vbfbryce View Post
    The shape on the sample presentation slide is "type 12." I'm not sure what that constant refers to.

    Tin, do you know?
    It is an activeX TextBox. Kind of what I figured when I posted #12 above.

    Something like this should work.

    Code:
       Dim tb As Shape
       Set tb = pres.Slides(1).Shapes("TextBox1")
       If tb.Type = msoOLEControlObject Then
          'got it
          tb.OLEFormat.Object.Text = "hi"
       Else
          ' ohoh now what do I do?
       End If
    Last edited by TnTinMN; Mar 21st, 2014 at 02:10 PM.

  20. #20
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Import data from excel to a powerpoint label


  21. #21
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Import data from excel to a powerpoint label

    Gracias! I looked all over for that and wasn't finding it!

  22. #22

    Thread Starter
    New Member
    Join Date
    Mar 2014
    Posts
    9

    Resolved Re: Import data from excel to a powerpoint label

    Thanks to all!

    It works great, now i'm going to customize it the real template...

    I'm surely writing soon

Posting Permissions

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



Click Here to Expand Forum to Full Width