Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy Powerpoint Datasheet (Excel 2003 SP2)

    Hi all.
    A user has decided that PowerPoint files are not the best place to store the history of monthly City Flows (for over 100 Cities).
    Currently the history for each City is saved in a PowerPoint file for that City.
    The history is monthly flow and there are many years of data for each City.
    The ppt has a graph that shows some of the data for the city. The DataSheet in the ppt file has all the years of data.

    They have asked me to write an Excel macro that will open each ppt file in the folder and process it as follows:
    1) Copy the DataSheet in the ppt and paste it (Transposed -not linked) on a new tab in Excel. Each City on its own tab.
    2) Name the tab to correspond with the ppt name (which is the City name).
    3) Close the ppt and continue with the next until done.

    I did searches but could not find what I needed.
    They want to start maintaining the History in Excel. At this point, the charts are not a concern.

    I am hoping someone has the time to help me on this.
    I will attach the Excel example to this post and the PowerPoint file to the next message.

    Thanks,
    Chuck
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  2. #2
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    Here is the PowerPoint sample.

    For some reason I am not able to send the ppt.... I will try a third time.
    I get error message: Explanation: The request timed out before the page could be retrieved
    Attached Files Attached Files
    Chuck Reimer
    I'm from the Government and I'm here to help...

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    Doing it manually is probably faster than programming it. And it will teach the user a lesson. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    Hans,

    I agree.
    But since they asked, I figured I would ask...
    (thanks for all the work you save people)

    Have a GREAT day!
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    <P ID="edit" class=small>(Edited by sdckapr on 11-Oct-07 10:41. Moved long code to Attachment)</P>Does this code do what you want?

    It looks in the path Cath for files with a PPT extension (change as desired). It only finds the first chart and extracts it. If the PPT does not have a chart it lists "no chart" on the created sheet.

    Steve
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    Steve,

    I look forward to trying it! Thanks so much for taking the time.
    I do not have the time to do it manually for them and was nervous with the abilities of the person they were going to assign.
    I will give your code a shot.

    Thanks again,
    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    I forgot to mention, you need to add references In VB to powerpoint and graph.

    In VB, Tools - references

    Then scroll to the Microsoft Powerpoint Object library and check it. The scroll to the Microsoft Graph Object library and check it.

    Or you must modify the code to DIM those particular variables as "Objects" instead of the particular objects in the references. I prefer the "reference route" since it allows prompting from the object model when creating the code.

    Steve

  8. #8
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy Powerpoint Datasheet (Excel 2003 SP2)

    Steve,

    Your code worked beautifully. It did exactly as I requested. One of the powerpoints (in the middle of the pack) had to be left out because all after that one had truncated data (that file must of had internal problems).
    No idea why, but easy to find the trouble maker.
    I showed the result to the user and they were very very happy. Of course then they changed their story ("actually I wanted the datasheet AND the graph"). I did laugh and indicate that they could take it from here.

    Many many thanks!

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

Posting Permissions

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