Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel OLE data in PPT reverting to OLD data after macro updation
Hi,
Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#2
|
|||
|
|||
I don't follow what's going on here, but let me ask this: do you call the OLE object's Update method before closing the app/releasing the reference to the object's variable? What you're describing sounds like what happens when you don't do this. In article , Hari Prasadh wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India ----------------------------------------- Steve Rindsberg, PPT MVP PPT FAQ: www.pptfaq.com PPTools: www.pptools.com ================================================ |
#3
|
|||
|
|||
Hari,
First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#4
|
|||
|
|||
Hi Steve/Brian,
Oops, sorry if I misled anyone. I got the idea (of controlling PPT from excel) and great parts of the syntax from Jon and then "meshed" it with snippets of other NG codes to customise it to my requirements. So bug is in my meshing (and incorrect understanding of the same) and not with Jon's code. do you call the OLE object's Update method before closing the app/releasing the reference to the object's variable? Im not sure what you mean by Update method. Please tell me about the same. I have 2 sets of codes: - a) First is I receive CSV file having some data. I transpose, sort perform Statistical tests on it and get it in to a form where it could be readily pasted in to PPT. This is one macro. b) Second is taking the formatted range in the CSV file above and calling a SUB which would paste this Data in to an already existing Excel chart OLE object within a PPT (having a single slide). (The PPT file may or may not be open depending on whether am updating data of other objects in the slide or not). It is the part b) with which Im having the present problem and am posting part b) below. (Latest update : - My colleagues included a line of code below - ograph.save - (ograph is the name of the excel workbook which is an OLE in PPT) which they claimed to solve the problem I posted, but I would still like to double check from you. Steve, when you said "update" do you mean save the object?) Option Explicit Public oPPTApp1 As PowerPoint.Application Dim oPPTShape1 As PowerPoint.Shape Dim rngNewRange1 As Excel.Range Dim oGraph1 As Workbook Dim Excelwksheet As Worksheet Dim Excelchartsheet As chart Dim path As String Dim PPT As PowerPoint.Presentation Dim a as Double Dim b as Double Dim c as Double Dim d as Double Sub UpdateExcelDataChart(filepath As String, objName As String, flag As Integer, sheetname As String, chartname As String) Set oPPTApp1 = CreateObject("PowerPoint.Application") oPPTApp1.Visible = msoTrue If flag = 0 Then oPPTApp1.Presentations.Open (filepath) Else End If 'There is a single slide in the presentation so can afford to use slides(1) With oPPTApp1.ActivePresentation.Slides(1) For Each oPPTShape1 In .Shapes 'objname is the name of the object within PPT whose data needs to be updated. If oPPTShape1.Name = objName Then 'a, b, c and d are being used because Excel OLE objects in PPT have a habit on going for getting dislocated while we are updating them. a = oPPTShape1.Top b = oPPTShape1.Left c = oPPTShape1.Height D = oPPTShape1.Width If oPPTShape1.OLEFormat.progID = "Excel.Chart.8" Then Set oGraph1 = oPPTShape1.OLEFormat.Object Set Excelwksheet = oGraph1.Worksheets(sheetname) ' Before this sub was called we already have some data in the clipboard hence pasting below. Excelwksheet.Range("B1").PasteSpecial xlPasteValues 'Though am just updating the data in a workbook for the chart to get updated am forced to activate ' the chart below as the view remains in worksheet based on above pasting. Set Excelchartsheet = oGraph1.Charts(chartname) Excelchartsheet.Activate oPPTShape1.LockAspectRatio = msoFalse oPPTShape1.Top = a oPPTShape1.Left = b oPPTShape1.Height = c oPPTShape1.Width = D End If End If Next oPPTShape1 End With Application.DisplayAlerts = False oPPTApp1.ActivePresentation.SaveAs ("D:\Dummy\NRAUTE001.ppt") oPPTApp1.ActivePresentation.Close End Sub Thanks a lot, Hari India "Brian Reilly, MS MVP" wrote in message ... Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: |
#5
|
|||
|
|||
He he. Read back aways, and you'll see that Hari abandoned my simpler
suggestion to update the charts in Excel and then put them into Ppt. He prefers doing it the hard way.... Hari's trying to update Excel charts in Excel workbook OLE objects which are embedded in PowerPoint. I've been strangely silent on this topic. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Reilly, MS MVP wrote: Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#6
|
|||
|
|||
Jon,
You aren't usually silent. Strange, always! Just like me. (vbg) Brian Reilly, MVP On Tue, 02 Aug 2005 08:22:41 -0400, Jon Peltier wrote: He he. Read back aways, and you'll see that Hari abandoned my simpler suggestion to update the charts in Excel and then put them into Ppt. He prefers doing it the hard way.... Hari's trying to update Excel charts in Excel workbook OLE objects which are embedded in PowerPoint. I've been strangely silent on this topic. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Reilly, MS MVP wrote: Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#7
|
|||
|
|||
Hi Jon,
He prefers doing it the hard way.... As I said in the one of the earlier posts, the charts/graphs in each of the slides have too much of custom formatting along with charts being of different types, Stacked/simulating average line/Pie chart, Color of chart, Placement of charts in the slides, font of the labels and hundreds of other stuff. Also, this same stuff needs to be done for tons of slides, so making charts in excel is too much of a pain (will require more coding). I got the idea of updating charts template charts with new data (and saving it with new name) from MS site where they have provided code for Updating an MS graph applet. But it seems now have run in to other limitations. Thanks a lot, Hari India "Jon Peltier" wrote in message ... He he. Read back aways, and you'll see that Hari abandoned my simpler suggestion to update the charts in Excel and then put them into Ppt. He prefers doing it the hard way.... Hari's trying to update Excel charts in Excel workbook OLE objects which are embedded in PowerPoint. I've been strangely silent on this topic. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Reilly, MS MVP wrote: Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#8
|
|||
|
|||
Oops, sorry if I misled anyone.
No, you just gave Brian a welcome opportunity to tease Jon. Not that anyone needs to MAKE opportunities for Brian to do that. ;-) Nor for me to tease Brian. We're all immune to one another, no worries. Im not sure what you mean by Update method. Please tell me about the same. I think that comes of my misunderstanding what you're up to. When automating MSGraph or Excel objects from within PPT, you need to call Update before closing the app or else the data isn't updated in the OLE object. The next time you activate it, it'll hold the old data and the chart will revert to whatever it previously was before you modified it. It sounds as though your colleagues have set us straight on this. Especially if the original object in PPT is linked to the excel data. Saving would be necessary, yes. I have 2 sets of codes: - a) First is I receive CSV file having some data. I transpose, sort perform Statistical tests on it and get it in to a form where it could be readily pasted in to PPT. This is one macro. b) Second is taking the formatted range in the CSV file above and calling a SUB which would paste this Data in to an already existing Excel chart OLE object within a PPT (having a single slide). (The PPT file may or may not be open depending on whether am updating data of other objects in the slide or not). It is the part b) with which Im having the present problem and am posting part b) below. (Latest update : - My colleagues included a line of code below - ograph.save - (ograph is the name of the excel workbook which is an OLE in PPT) which they claimed to solve the problem I posted, but I would still like to double check from you. Steve, when you said "update" do you mean save the object?) Option Explicit Public oPPTApp1 As PowerPoint.Application Dim oPPTShape1 As PowerPoint.Shape Dim rngNewRange1 As Excel.Range Dim oGraph1 As Workbook Dim Excelwksheet As Worksheet Dim Excelchartsheet As chart Dim path As String Dim PPT As PowerPoint.Presentation Dim a as Double Dim b as Double Dim c as Double Dim d as Double Sub UpdateExcelDataChart(filepath As String, objName As String, flag As Integer, sheetname As String, chartname As String) Set oPPTApp1 = CreateObject("PowerPoint.Application") oPPTApp1.Visible = msoTrue If flag = 0 Then oPPTApp1.Presentations.Open (filepath) Else End If 'There is a single slide in the presentation so can afford to use slides(1) With oPPTApp1.ActivePresentation.Slides(1) For Each oPPTShape1 In .Shapes 'objname is the name of the object within PPT whose data needs to be updated. If oPPTShape1.Name = objName Then 'a, b, c and d are being used because Excel OLE objects in PPT have a habit on going for getting dislocated while we are updating them. a = oPPTShape1.Top b = oPPTShape1.Left c = oPPTShape1.Height D = oPPTShape1.Width If oPPTShape1.OLEFormat.progID = "Excel.Chart.8" Then Set oGraph1 = oPPTShape1.OLEFormat.Object Set Excelwksheet = oGraph1.Worksheets(sheetname) ' Before this sub was called we already have some data in the clipboard hence pasting below. Excelwksheet.Range("B1").PasteSpecial xlPasteValues 'Though am just updating the data in a workbook for the chart to get updated am forced to activate ' the chart below as the view remains in worksheet based on above pasting. Set Excelchartsheet = oGraph1.Charts(chartname) Excelchartsheet.Activate oPPTShape1.LockAspectRatio = msoFalse oPPTShape1.Top = a oPPTShape1.Left = b oPPTShape1.Height = c oPPTShape1.Width = D End If End If Next oPPTShape1 End With Application.DisplayAlerts = False oPPTApp1.ActivePresentation.SaveAs ("D:\Dummy\NRAUTE001.ppt") oPPTApp1.ActivePresentation.Close End Sub Thanks a lot, Hari India "Brian Reilly, MS MVP" wrote in message ... Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: ----------------------------------------- Steve Rindsberg, PPT MVP PPT FAQ: www.pptfaq.com PPTools: www.pptools.com ================================================ |
#9
|
|||
|
|||
MSGraph is not MSExcel. The data area behind an MSGraph chart is not an
excel worksheet. Perhaps that is your problem - trying to treat them the same. -- Regards, Tom Ogilvy "Hari Prasadh" wrote in message ... Hi Jon, He prefers doing it the hard way.... As I said in the one of the earlier posts, the charts/graphs in each of the slides have too much of custom formatting along with charts being of different types, Stacked/simulating average line/Pie chart, Color of chart, Placement of charts in the slides, font of the labels and hundreds of other stuff. Also, this same stuff needs to be done for tons of slides, so making charts in excel is too much of a pain (will require more coding). I got the idea of updating charts template charts with new data (and saving it with new name) from MS site where they have provided code for Updating an MS graph applet. But it seems now have run in to other limitations. Thanks a lot, Hari India "Jon Peltier" wrote in message ... He he. Read back aways, and you'll see that Hari abandoned my simpler suggestion to update the charts in Excel and then put them into Ppt. He prefers doing it the hard way.... Hari's trying to update Excel charts in Excel workbook OLE objects which are embedded in PowerPoint. I've been strangely silent on this topic. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Brian Reilly, MS MVP wrote: Hari, First mistake is using any of Jon P's instructions. (VBG) Jon is a good friend and a well respected MVP. But that doesn't stop us from teasing him (g). As Steve said we aren't sure what you are doing here (exactly by code) so can you post the relevant parts. Then we can let Jon Peltier answer his own mess (vbg). Couldn't resist Jon, Hari is a recent regular in the PPT NG. Hari, Jon is a regular stop in in the PPT NewsGroup and works regularly in Excel and PPT integration all the time. Brian Reilly, MVP On Tue, 2 Aug 2005 01:18:42 +0530, "Hari Prasadh" wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#10
|
|||
|
|||
Hi Tom,
Thanks for jumping in. Actually I wanted to say that the idea of updating a range in a "template" I got from a MS KB link article for updating MS graph applets. I saw the syntax behind the same, especially the inter application programming syntax and way the specified object is activated. After that I got in to "cutomisation" mode for using it to update data behind excel graph objects. So in effect am not treating Msgraph same as MsCharts (Some time back Jon gave me a neat session on the differences between the 2) Thanks a lot, Hari India "Tom Ogilvy" wrote in message ... MSGraph is not MSExcel. The data area behind an MSGraph chart is not an excel worksheet. Perhaps that is your problem - trying to treat them the same. -- Regards, Tom Ogilvy "Hari Prasadh" wrote in message ... |
#11
|
|||
|
|||
Hari -
Is all you're doing changing the data that the embedded Excel workbook's chart is based upon? Maybe I'm being dense, but I don't think you need to activate the OLE object, or use copy-paste. I created an Excel workbook that had a worksheet ("Sheet1") with data in A1:A5, and a chart sheet ("Chart 1") based on this data. I shrank the chart sheet, then copied it and pasted it as an Excel Chart Object into a PowerPoint slide. Then I closed the Excel workbook in Excel without saving, so it's gone. I fiddled around with the PPT and XL Immediate windows, then came up with this short little procedure, which I ran from another Excel workbook: Sub GetXLinPPT() Dim ppApp As PowerPoint.Application Dim xlWbk As Excel.Workbook ' OLE object in ppApp Set ppApp = GetObject(, "PowerPoint.Application") Set xlWbk = _ ppApp.ActivePresentation.Slides(1).Shapes(1).OLEFo rmat.Object xlWbk.Worksheets("Sheet1").Range("A1:A5").Value = _ WorksheetFunction.Transpose(Array(5, 4, 3, 2, 1)) Set xlWbk = Nothing Set ppApp = Nothing End Sub The data in the OLE worksheet must have updated, because the chart updated, and stayed updated. Then all I did was save the PowerPoint file. When I reopened it, the changes were still there. By not opening the OLE object, you might prevent the resizing and repositioning it is susceptible to. This technique worked well enough that I might even have to start using it. Hear that, Brian? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Hari Prasadh wrote: Hi, Im cross-posting/multi-posting this in the hope that I could get a solution. I am facing a very strange problem (both in Office 2002 and 2003) with Excel OLE charts in PPT. (Im programming with excel as base) I am using code zilched from Jon P's site in order to copy data from an excel sheet and activate a PPT application (and Slide) and an Excel OLE object within the slide and then updating the data in it and then saving this template with a NEW NAME. Im using this method to update a single slide template tons of time and each time saving it with different (but well-defined) names. Situation: - The new data gets pasted in the Excel worksheet behind the chart (within PPT) and then the chart also gets updated and then I do a Save As (using code) to save it with a new name and in a new location and then close the file. I then exit out of the code and then open the file manually and in the slide show mode can see the UPDATED data but if I try to get in to editing mode of the Excel OLE chart within PPT then the chart SUDDENLY displays the old data and the excel worksheet also displays the old data?? If I escape out of the OLE and do Ctrl + Z then it shows the data updated through Macro. Couple of my colleagues were running tests and they see that if they save the file manually after updating data through Macro then the same problem doesnt recur? Im not very sure why it is so and nor Do I know whether the above observation about manual saving is correct for all situations. For me manual saving is not an option. We are also experiencing the same problem in updating excel worksheets within PPT's. Can anybody please guide me as to what is happening here? Regards, Hari India |
#12
|
|||
|
|||
Hi Jon,
Its fantabulous. I tried it on one slide and it works flawlessly and no objects get dislocated and neither repositioned . Moreover my code also becomes less cluttered as now I wont have to remember object positions and what not . In the old way of copy-paste even after remembering and forcibly applying the positions the stuff was far from perfect and considerable manual formatting was required. Just to tell you in the old method I was also having problems of view of the charts changing from charts to the worksheet behind the data, which caused further aggravation for me. Im surprised that this simple solution wasnt thought by before!!! (Brian, cant resists from pulling your legs - I can hide under the cloak of being a beginner but what about you!!) This technique worked well enough that I might even have to start using it. Hear that, Brian? 2 points I would like to add: - a) In one of the earlier posts in PPT group I asked for project mgmt of PPT automation using Excel and Brian asked a very important question as to the base application (excel or PPT) im using for programming. I said excel because I could pinch Jon's pre-built tutorials. I think one more important needs to be added to the list which is as to whether one is creating objects on the fly in PPT or whether one has a template in which one is populating new data using Jon's latest method. The second would be extremely helpful when the slides involve lot of custom/individual formatting differences across objects (within slides/across slides). I think that way the if one has a pre-built template the one doesnt have to "code" those custom formatting within each individual sub. Less code, less debugging headache. b) Jon if possible please add this new way of your to the web-site. Others wont have to break their heads with PPT object dislocations. Regards, Hari India |
#13
|
|||
|
|||
Hari Prasadh wrote:
Hi Jon, Its fantabulous. :) I tried it on one slide and it works flawlessly and no objects get dislocated and neither repositioned . Moreover my code also becomes less cluttered as now I wont have to remember object positions and what not . In the old way of copy-paste even after remembering and forcibly applying the positions the stuff was far from perfect and considerable manual formatting was required. I was as surprised as you that it worked so nicely. Of course, my example was a simple column chart, but even the simple charts have problems with the OLE activate & paste techniques. Just to tell you in the old method I was also having problems of view of the charts changing from charts to the worksheet behind the data, which caused further aggravation for me. It is problems like this which have kept me away from using such objects in my projects, and which keep Brian on my case. Im surprised that this simple solution wasnt thought by before!!! (Brian, cant resists from pulling your legs - I can hide under the cloak of being a beginner but what about you!!) I usually just blame PowerPoint (Brian's a Ppt MVP, after all), but I see that now PowerPoint's been fixed, and we can all use it. This technique worked well enough that I might even have to start using it. Hear that, Brian? 2 points I would like to add: - a) In one of the earlier posts in PPT group I asked for project mgmt of PPT automation using Excel and Brian asked a very important question as to the base application (excel or PPT) im using for programming. I said excel because I could pinch Jon's pre-built tutorials. I think one more important needs to be added to the list which is as to whether one is creating objects on the fly in PPT or whether one has a template in which one is populating new data using Jon's latest method. The second would be extremely helpful when the slides involve lot of custom/individual formatting differences across objects (within slides/across slides). I think that way the if one has a pre-built template the one doesnt have to "code" those custom formatting within each individual sub. Less code, less debugging headache. Definitely. My projects typically include one or more templates, with all of the boilerplate built in. If I have a few options, I either make a bigger template and remove what I don't need, or I make multiple templates. If the project involves Excel and PowerPoint, I usually use templates in XL and PPT, though the PowerPoint ones aren't really templates, but rather preformatted presentations with the various slides that will be needed. I guess before long we'll do it all with XML and XLS. b) Jon if possible please add this new way of your to the web-site. Others wont have to break their heads with PPT object dislocations. I actually thought of this approach based on an email from another person, after having followed this thread. It worked for him, and now it works for you, and both of you are clamoring for more pages on my web site. I'll see if my publisher wants to commission a few pages (oops, that's me, so I guess I'm doing it on my own time). I write the occasional article, though, and this is a pretty good topic for such an article. As I said, in my "free time", which means September or so. I'll be traveling then, and I can't do real work on the plane, but I can often use the time for reports and articles. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Excel Data from One Sheet to Another.... | Excel Discussion (Misc queries) | |||
How can deleted data reappear in a refreshed pivot table in Excel | Excel Discussion (Misc queries) | |||
pasting excel data in a powerpoint slide | Excel Discussion (Misc queries) | |||
How do I plot Excel data on a floorplan? | Excel Discussion (Misc queries) | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions |