VBA Power Point Changing X Axis Labels

[maccten2000]

Hi All,

I have a report i am trying to automate. In essense it pulls data from MS Access, generates a pivot In Excel whose values i will then use to update several graphs all sitting behind a button.

I have encountered a problem i was wondering if you could help me with

The X-Axis on the graphs is variable depending on the information pulled back in the query. I cant seem to reference it in my code like i can the actual values. The code is being executed in Excel and its below (this is just a rough sketch) based off of someone elses code i found online

I was wondering if anyone has ever done anything like this before

Thanks for your time and i hope you have a good weekend

Dim oPPTApp As PowerPoint.Application
Dim oPPTShape As PowerPoint.Shape
Dim oPPTFile As PowerPoint.Presentation
Public oGraph As Graph.Chart
Dim SlideNum As Integer

Sub PPGraphMacro()
    
    ' Declare Variables
    Dim strPresPath As String
    Dim strExcelFilePath As String
    Dim strNewPresPath As String
    Dim oRange As Graph.Range
    Dim strVal As String
    
    
    ' Set variables
    strPresPath = "C:\Documents and Settings\user\Desktop\Automation\SI Compliance\SI Compliance.ppt"
    strNewPresPath = "C:\Documents and Settings\user\Desktop\Automation\SI Compliance\SI Compliance_test.ppt"
    Set oPPTApp = CreateObject("PowerPoint.Application")
    oPPTApp.Visible = msoTrue
    Set oPPTFile = oPPTApp.Presentations.Open(strPresPath)
    SlideNum = 1
    oPPTFile.Slides(SlideNum).Select
    
    ' Select First Graph
    Set oPPTShape = oPPTFile.Slides(SlideNum).Shapes("Object 152")
    Set oGraph = oPPTShape.OLEFormat.Object
    
    
    ' Loop through each cell until you get a null
    
    With oGraph.Application.DataSheet

    For Each oRange In .Range("a1:d1")
         strVal = oRange.Value
      Next oRange
    End With

    'oGraph.Application.DataSheet.Range("A1").Value = Cells(35, 3).Value
    

    oGraph.Application.Update
    oGraph.Application.Quit
  
    oPPTFile.SaveAs strNewPresPath
    oPPTFile.Close
    oPPTApp.Quit
    
    Set oGraph = Nothing
    Set oPPTShape = Nothing
    Set oPPTFile = Nothing
    Set oPPTApp = Nothing
    MsgBox "Presentation Created", vbOKOnly + vbInformation
End Sub

 

[SkipVought]

Hi,

What version PowerPoint? If 2007 or greater, your chart in PP IS an Excel chart. If not, it is MS Graph (YUK!) I'd be using an embedded Excel chart.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[maccten2000]

Hi Skip,

Thanks for the quick response. We are stuck with Powerpoint 2003 so im afraid I have to use the MS Graph Library to achieve this although im open to correction

Thanks

 

[SkipVought]

Your choice is to embed either...

1) MS Graph or
2) MS Excel Chart

A Chevrolet or a Cadillac?

In 2007 and greater, they remove the choice of the Chevy: whaaaa whaaaa!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[maccten2000]

Hi Skip,

I have been told that we have to use the Chevrolet as its being used for all of our reports. This is just the first one that we are trying to automate. Do you know how to edit the labels with VBA?

Thanks

 

[SkipVought]

No, but you can use this to help discover what properites are avaliable in your coded objects... faq707-4594

I fail to understand why you cannot set up your data in Excel, link a chart, format the way you want and then embed in PP.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[maccten2000]

Hi All,

Just in case anyone eas following this thread

I found the answer here on MS's website

http://support.microsoft.com/kb/267974

Thanks again