T
Todd Waldron
Hi all,
I have a procedure where I am using VBA to paste values into an existing PPT
chart object. The chart object type is Excel and it contains two worksheets,
one titled "Records Data" (containing the datasheet and chart graphic), and
the other titled "Chart1" (containing the solitary chart graphic). The
problem I'm having is once the procedure finishes the "Records Data"
worksheet is the last object selected and therefore is what displays on the
slide, and what I need is for the "Chart1" worksheet to display. I can fix
this manually in the PPT by simply double-clicking the chart on the slide and
selecting the "Chart1" worksheet, but I'd like to accomplish this step
through code.
I ran a macro in Excel to get a demonstration of what I'm looking to do and
it produced:
Sheets("Chart1").Select
I followed the object model in my automation and tried the following
examples but I get "Runtime Error 9 - Script out of range" for both:
wkbWorkBook2.Sheets("Chart1").Select
wkbWorkBook2.Worksheets("Chart1").Select
Below is the code for my 'paste' procedure, my attempts to select the
"Chart1" worksheet are commented out.
***Code Start***
'Paste
appPPT.ActiveWindow.Selection.SlideRange.Shapes("Object 7").Select
'Open and Invoke the Excel Slide Objects
Set pptShape = appPPT.ActiveWindow.Selection.ShapeRange(1)
Set wkbWorkBook2 = pptShape.OLEFormat.Object
Set wksChart = wkbWorkBook2.Charts(1)
Set wksWorkSheet2 = wkbWorkBook2.Worksheets("Records Data")
wksWorkSheet2.Range("B1").PasteSpecial
wkbWorkBook2.save
'Select "Chart1"
'wkbWorkBook2.Sheets("Chart1").Select
'wkbWorkBook2.Worksheets("Chart1").Select
appPPT.ActiveWindow.Selection.Unselect
'Close and release the Excel Slide Objects
Set pptShape = Nothing
wkbWorkBook2.Close
Set wkbWorkBook2 = Nothing
Set wksWorkSheet2 = Nothing
Set wksChart = Nothing
***Code End***
Can anyone shed light on how I might select "Chart1" in my procedure?
Thank you in advance.
Todd Waldron
Austin, Texas
I have a procedure where I am using VBA to paste values into an existing PPT
chart object. The chart object type is Excel and it contains two worksheets,
one titled "Records Data" (containing the datasheet and chart graphic), and
the other titled "Chart1" (containing the solitary chart graphic). The
problem I'm having is once the procedure finishes the "Records Data"
worksheet is the last object selected and therefore is what displays on the
slide, and what I need is for the "Chart1" worksheet to display. I can fix
this manually in the PPT by simply double-clicking the chart on the slide and
selecting the "Chart1" worksheet, but I'd like to accomplish this step
through code.
I ran a macro in Excel to get a demonstration of what I'm looking to do and
it produced:
Sheets("Chart1").Select
I followed the object model in my automation and tried the following
examples but I get "Runtime Error 9 - Script out of range" for both:
wkbWorkBook2.Sheets("Chart1").Select
wkbWorkBook2.Worksheets("Chart1").Select
Below is the code for my 'paste' procedure, my attempts to select the
"Chart1" worksheet are commented out.
***Code Start***
'Paste
appPPT.ActiveWindow.Selection.SlideRange.Shapes("Object 7").Select
'Open and Invoke the Excel Slide Objects
Set pptShape = appPPT.ActiveWindow.Selection.ShapeRange(1)
Set wkbWorkBook2 = pptShape.OLEFormat.Object
Set wksChart = wkbWorkBook2.Charts(1)
Set wksWorkSheet2 = wkbWorkBook2.Worksheets("Records Data")
wksWorkSheet2.Range("B1").PasteSpecial
wkbWorkBook2.save
'Select "Chart1"
'wkbWorkBook2.Sheets("Chart1").Select
'wkbWorkBook2.Worksheets("Chart1").Select
appPPT.ActiveWindow.Selection.Unselect
'Close and release the Excel Slide Objects
Set pptShape = Nothing
wkbWorkBook2.Close
Set wkbWorkBook2 = Nothing
Set wksWorkSheet2 = Nothing
Set wksChart = Nothing
***Code End***
Can anyone shed light on how I might select "Chart1" in my procedure?
Thank you in advance.
Todd Waldron
Austin, Texas