Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Worksheet stored within Power Point
Power point does not have a programming forum, so I am forced to post here.
My presentation include an embedded Excel chart. During presentation, I would like to modify some parameters and show how the chart changes. I could not find any way to do this, through help files, object browser or online sources. Can someone gve me pointer to start playing with this worksheet? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Worksheet stored within Power Point
I assume you used PasteSpecial to put the chart into Power Point as an excel object so the chart is an ActiveX object. 1) When using one office product in another office product you need to add the reference library to the VBA application to be able to use all the commands. From Power Point VBA menu Tools - References - Microsoft Excel XX.X objct Library Make sure you check the box next to the objet and press the OK button 2) Here is some code to get you started. Sub test() Dim Excelbk As Excel.Workbook Dim Excelchart As Excel.Chart Set Myslide = ActivePresentation.Slides(1) For Each myshape In Myslide.Shapes Set Excelbk = myshape.OLEFormat.Object Set Excelchart = Excelbk.ActiveChart Next myshape End Sub If you get an error in the above code verify the Reference is checked in the VBA menu. Once you have the excel chart you should be able to use the object like any excel chart. Chart are hard objects to work with and should refer to a specific forum on excel charts for help. I've done a good number of macros using excel charts and still have trouble get the exact syntax. I usually refer back to one of my old macros for charting when I run into problems. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188903 http://www.thecodecage.com/forumz/chat.php |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Worksheet stored within Power Point
Thanks Joel, I was able to access the excel sheet this way. However, what I
wanted to do was either not possible, or needs a lot more work. In short, I have a range of data and the chart pasted on the same slide. Being a highly optimistic fellow, I was hoping that when I change the data, the chart will be updated (as they are linked in the original workbook). Silly me. I think I am going to try again by pasting them as links, then change data in thesource file and update links. This ought to work I guess, if can figure out how to open the source invisibly and update links during presentation show. Thanks for the tip though. "joel" wrote: I assume you used PasteSpecial to put the chart into Power Point as an excel object so the chart is an ActiveX object. 1) When using one office product in another office product you need to add the reference library to the VBA application to be able to use all the commands. From Power Point VBA menu Tools - References - Microsoft Excel XX.X objct Library Make sure you check the box next to the objet and press the OK button 2) Here is some code to get you started. Sub test() Dim Excelbk As Excel.Workbook Dim Excelchart As Excel.Chart Set Myslide = ActivePresentation.Slides(1) For Each myshape In Myslide.Shapes Set Excelbk = myshape.OLEFormat.Object Set Excelchart = Excelbk.ActiveChart Next myshape End Sub If you get an error in the above code verify the Reference is checked in the VBA menu. Once you have the excel chart you should be able to use the object like any excel chart. Chart are hard objects to work with and should refer to a specific forum on excel charts for help. I've done a good number of macros using excel charts and still have trouble get the exact syntax. I usually refer back to one of my old macros for charting when I run into problems. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188903 http://www.thecodecage.com/forumz/chat.php . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Worksheet stored within Power Point
That is not going to work. The entire workbook is in Power Point and you are just viewing that chart. Double clik the chart object and you will get the entire workbook. You have to change the data inside the Power Point Object. You can change these items inside a macro in Power Point. that is why I created a variable for the workbook in my macro. You can access the sheets and the ranges on the sheet using my code Set Excelbk = myshape.OLEFormat.Object Excelbk.sheets("sheet1").Range("A1") = 5 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188903 http://www.thecodecage.com/forumz/chat.php |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Accessing Worksheet stored within Power Point
Many thanks Joel. Your version did not work, but I got it done still due to
your pointers. I guess I should have mentioned that I use 2007, and the chart I pasted did not appear as an OLE object, OLEFormat method gave an error. In the end, the following worked: Set ch2 = ActivePresentation.Slides(2).Shapes(2) Set wk = ch2.Chart.ChartData.Workbook With wk.ActiveSheet.Range("Ali") .Value = .Value + 1 End With ch2.Chart.Refresh Cheers, "joel" wrote: That is not going to work. The entire workbook is in Power Point and you are just viewing that chart. Double clik the chart object and you will get the entire workbook. You have to change the data inside the Power Point Object. You can change these items inside a macro in Power Point. that is why I created a variable for the workbook in my macro. You can access the sheets and the ranges on the sheet using my code Set Excelbk = myshape.OLEFormat.Object Excelbk.sheets("sheet1").Range("A1") = 5 -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=188903 http://www.thecodecage.com/forumz/chat.php . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accessing data stored in the worksheet (named range) in .xla file | Excel Programming | |||
Excel to Power Point? | Excel Programming | |||
Problem importing excel worksheet in Word and or Power point | Excel Discussion (Misc queries) | |||
how to use power point | Excel Discussion (Misc queries) | |||
Linking an object in power point to a excel worksheet | Excel Discussion (Misc queries) |