Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am trying to export a chart (only the currently viewed chart) when selected by a user after pressing a button.
Currently I have an input box (with drop down enabled) to select which chart is shown. Only the chart selected in the input box is shown (all others are conditionally hidden).
I want the button to export a given chart to excel. I can have the button function as follows:
SUB ReportSelection
SET chart=ActiveDocument.GetSheetObject("CH01")
SET p = chart.GetProperties
chart.SendToExcel
END SUB
However I would have to create a separate button for each chart.
If I did this:
SUB ReportSelection
For i = 0 To ActiveDocument.NoOfSheets - 1
Set MySheet = ActiveDocument.GetSheet(i)
MyCharts=MySheet.GetSheetObjects
For X =lbound(MyCharts) to ubound(MyCharts)
ActiveDocument.Sheets(i).Activate
Set obj = ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)
IF Obj.GetObjectType = 10 Then
SET chart=ActiveDocument.GetSheetObject(MyCharts(X).GetObjectId)
SET p = chart.GetProperties
chart.SendToExcel
End if
Next
Next
End Sub
The Macro goes through and prints out all four charts I have created.
Is there a way to only print out the current chart the user is viewing?
Any help would be greatly appreciated. Thanks!
Try creating a variable with the chart ID that corresponds with your conditional layout. Then use the variable to refer to the sheet object in the macro.
For example, if your conditional layout is vShow=0 for CH01 and vShow=1 for CH02, then your variable would be something like vChart = if(vShow=0,'CH01',if(vShow=1,'CH02')).
Does that make sense?
Hi Rebecca,
That does make sense. However it still is not working for me. I reference the variable being changed every time i select a report in the input box (ReportSelection) but it does not do anything when prompted. Here is what I came up with;
SUB RunReport
vChart="CH01"
if ReportSelection="Projected vs. Actual" Then
vChart="CH01"
elseif ReportSelection="FMHER Summary" then
vChart="CH02"
elseif ReportSelection="Signed-to-Date" then
vChart="CH03"
elseif ReportSelection="Close-Out and Re-Align Analysis" then
vChart="CH04"
else vChart="CH00" end if
SET chart=ActiveDocument.GetSheetObject(vChart)
SET p = chart.GetProperties
chart.SendToExcel
END SUB
Any ideas?
Here is an example of what I mean. Hope it helps.
-Becca
Becca, thank you so much! It works now!
I had to create another variable to set the Chart ID before processing it with the macro. Here is the macro I used:
SUB RunReport
Set t = ActiveDocument.Variables("TableID")
chartID=t.GetContent.String
SET chart=ActiveDocument.GetSheetObject(chartID)
SET p = chart.GetProperties
chart.SendToExcel
END SUB
Thanks again!!!
No problem - glad you got it to work!
Hi Rebecca,
Thanks for the post it helped me to get some part of my requirement.
Is it possible to Export the Multiple charts which are currently viewed by the User.
For instance I have like 20 charts in a Single sheet and after making selections by the user, User could see only 5 charts out of them and wants to export them to PDF.
Thanks in advance.