locked
Excel Chart Effect Customization is not working RRS feed

  • Question

  • I'm working with the following code to create a visually attractive chart object in Excel to be pasted into a Word document.  Many of the features that are supposed to be associated with the chart object do not work, however.  Is there something I'm doing wrong here? 

            Dim objExcel As New Excel.Application
            Dim objWrkBk As Excel.Workbook
            Dim objSheet As Excel.Worksheet
            Dim Range As Excel.Range
            Dim Chart As Excel.Chart
    
    
            objExcel = New Excel.Application
            objWrkBk = objExcel.Workbooks.Add
            objSheet = objWrkBk.Sheets.Add
            objSheet = objWrkBk.Sheets(1)
            Chart = objExcel.Charts.Add
            objExcel.Visible = True
            '-------------------------------
           
    
            With Chart
                .ChartType = Excel.XlChartType.xlLine
                .HasTitle = True
                .SetSourceData(Range, PlotBy:=Excel.XlRowCol.xlColumns)
                .ChartTitle.Text = Title
                .Location(Excel.XlChartLocation.xlLocationAsObject, objSheet.Name)
                .Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop 'DOES NOT WORK
                .ChartObjects.MarkerStyle = 8 'DOES NOT WORK
                .ChartObjects.MarkerSize = 5 'DOES NOT WORK
    
            End With
    
    
    'NONE OF THE FOLLOWING WORKS...
            Chart.ChartArea.Format.Glow.Color.SchemeColor = 8
            Chart.ChartArea.Format.Glow.Radius = 15
            Chart.ChartArea.Format.Glow.Color.RGB = RGB(50, 50, 90)
            Chart.ChartArea.Format.Glow.Color.TintAndShade = 0.5
            Chart.ChartArea.Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow
            Chart.ChartArea.Format.SoftEdge.Type = MsoSoftEdgeType.msoSoftEdgeType2
            Chart.ChartArea.Format.Shadow.Visible = MsoTriState.msoCTrue
            Chart.ChartArea.Format.Shadow.Blur = 25
            'Chart.ChartArea.Format.Fill.TwoColorGradient(MsoGradientStyle.msoGradientVertical, 10)
            Chart.ChartArea.Format.Fill.PresetGradient(MsoGradientStyle.msoGradientVertical, 10, MsoPresetGradientType.msoGradientFog)
            Chart.ChartArea.Format.Line.Transparency = 10
            Chart.Elevation = 10
            Chart.SeriesCollection(1).MarkerStyle = Excel.XlMarkerStyle.xlMarkerStyleCircle
            Chart.SeriesCollection(1).MarkerSize = 4
    • Moved by Riquel_Dong Monday, May 18, 2009 1:08 AM redirect to Office Development discussion (From:Visual Basic Interop and Upgrade)
    Monday, May 11, 2009 4:48 PM

Answers

  • The following is what I eventually came up with:

     
     '---------------------------------------------------------Nitrate Graph
            '
            '
            'Build the nitrate graph
            Dim objExcel As New Excel.Application 'create an Excel application object
            Dim objWrkBk As Excel.Workbook 'create a workbook
            Dim objSheet As Excel.Worksheet 'create a worksheet
            Dim Range As Excel.Range 'create a range
            Dim Chart As Excel.Chart 'create a chart
            Dim chartObjects As Excel.ChartObjects = Nothing 'create a chartObjects instance
            Dim existingChartObject As Excel.ChartObject = Nothing 'create a chartObject instance
    
            objExcel = New Excel.Application 'start the Excel application
            objWrkBk = objExcel.Workbooks.Add 'add the workbook to this excel application
            objSheet = objWrkBk.Sheets.Add 'add the worksheet to the workbook object
            objSheet = objWrkBk.Sheets(1) 'objSheet is the first sheet in the workbook
            Chart = objExcel.Charts.Add 'add a chart to the Excel application
            'objExcel.Visible = True
    
            '''''''''''''''''''''''''''''''''''''''''''''
            'Create a macro to be run within the excel  '
            'application to delete a series that is     '
            'otherwise undeletable.                     '
            '''''''''''''''''''''''''''''''''''''''''''''
    
            Dim macro As VBIDE.VBComponent 'create a macro object
            Dim sCode As String 'create a string to hold the macro programming
    
    retry:  'A point at which the macro creation is retried if unsuccessful
    
            'Add in a macro so that Excel will delete the day as the series.
            Try
                macro = objWrkBk.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule) 'place the macro into the workbook
            Catch ex As Exception 'if an error occurs, catch it and do the following:
                'If the user has not enabled access to Visual Basic a Run-time error will occur.
                'Details on this error can be found here:  http://support.microsoft.com/kb/282830/en-us
                'Run-time error '6068': Programmatic Access to Visual Basic Project is not trusted 
                Dim Response As Integer
                'if the error is caught, a message box will display with the following text instructions on how to fix the problem.
                Response = MessageBox.Show("Access is denied from Microsoft Excel.  You need to do the following:" & vbCr & vbCr & _
                                "   1. Open Microsoft Office Excel 2007.  Click the Microsoft Office" & vbCr & _
                                "         button, and then click Excel Options." & vbCr & vbCr & _
                                "   2. Click the Trust Center tab, and then click Trust Center Settings." & vbCr & vbCr & _
                                "   3. Click the Macro Settings tab, click to select the Trust access to " & vbCr & _
                                "       the VBA project object model check box, and then click OK." & vbCr & vbCr & _
                                "   4. Click OK. " & vbCr & vbCr & _
                                "Restart IX Report Gen.", "Security Access Denied", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation)
                If Response = 4 Then 'If the user selected to retry they will be taken to the retry point above
                    GoTo retry
                Else
                    End 'If the user cancels the program simply ends.
                End If
            End Try
    
            'The macro code is put into the sCode string.
            'Line by line, the code below is described:
    
            'Sub Macro2()
            'ActiveSheet.ChartObjects(""Chart 1"").Activate     'Activate the chart in the sheet
            'ActiveChart.SeriesCollection(1).Select             'Select the chart's 1st data series
            'Selection.Delete                                   'Delete the chart's 1st data series
            'End Sub                                            'End the macro
    
            sCode = "Sub Macro2()" & vbCr & "ActiveSheet.ChartObjects(""Chart 1"").Activate" _
                    & vbCr & "ActiveChart.SeriesCollection(1).Select" _
                    & vbCr & "Selection.Delete" _
                    & vbCr & "End Sub"
            'The sCode string is placed into the empty macro
            macro.CodeModule.AddFromString(sCode)
    
    
            'Populate the Nitrate Avegare Array
            Dim HeadingArray(1, 1) As String 'A HeadingArray is created to place headings into the Excel sheet
            HeadingArray(0, 0) = "Day" 'Heading 1 is Day
            HeadingArray(0, 1) = "Nitrate Avg" 'Heading 2 is Nitrate Avg
            Range = objSheet.Range("A1", "B1") 'Range is assigned to the cells A1 and B1
            Range.Value = HeadingArray 'The value of the range is set to the value of the HeadingArray.  A1=Day and B1=Nitrate Avg
            'Range is now assigned to the cells from A2 to the B column and 'as many rows as are in the reporting month plus 1
            'to accomodate the heading row.
            Range = objSheet.Range("A2", "B" & (DaysInMonth + 1))
            Range.Value = DailyAverageArray 'The value of Range is now assigned the values of DailyAverageArray
            Range = objSheet.Range("A1", "B" & DaysInMonth + 1) 'Range is reassigned to select the whole edited area from A1 to B#
    
            'This is the title later used for the chart.  An example of what the title would
            'say if it were April is, Daily Nitrate Averages for April
            Dim Title As String = "Daily Nitrate Averages for " & Month
    
            Chart.Location(Excel.XlChartLocation.xlLocationAsObject, objSheet.Name) 'The chart is placed into the worksheet
            ' Get the ChartObjects collection for the sheet.
            chartObjects = objSheet.ChartObjects() 'chartObjects is assigned to this sheet
    
    
            ' Get the chart to modify.  This is the first item in chartObjects
            existingChartObject = chartObjects.Item(1)
    
            'Custom settings are assigned to the chart using the With keyword
            With existingChartObject
                .Chart.ChartType = Excel.XlChartType.xlLine 'This is a line graph
                .Chart.SetSourceData(Range, PlotBy:=Excel.XlRowCol.xlColumns) 'we plot by the columns selected--this gives us our headings as names
                .Chart.HasLegend = True 'give the chart a legend
                .Chart.HasTitle = True 'tell the chart it has a title
                .Chart.ChartTitle.Text = Title 'give the chart its title (declared and assigned earlier)
                .Shadow = True 'Give the chart a shadow effect
                .Chart.ChartArea.Shadow = True 'Give the chart area a shadow effect
                .Chart.ChartArea.Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'assign an outer shadow style
                .Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop 'position the legend at the top of the chart
                .Chart.ChartArea.Format.Glow.Radius = 10 'add a glow with a radius of 10
                .Chart.ChartArea.Format.Glow.Color.RGB = RGB(90, 90, 90) 'set the color of the glow to a gray color
                .Chart.ChartArea.Format.Fill.Visible = True 'make the fill on the chart visible
                'make the fill a gradient that fades vertically from one color to the next with a Fog style.
                '.Chart.ChartArea.Format.Fill.PresetGradient(MsoGradientStyle.msoGradientVertical, 2, MsoPresetGradientType.msoGradientFog)
                'give the chart major gridlines on it's x axis
                .Chart.Axes(Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = True
            End With 'Finish working with existingChartObject
    
            'from the perspective of the sheet, set the Width and Height to 500 X 300
            objSheet.ChartObjects(1).Width = 500
            objSheet.ChartObjects(1).Height = 300
    
            'Format the Chart's 2nd data series
            With existingChartObject.Chart.SeriesCollection(2)
                .Shadow = True 'give it a shadow
                .Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'set the shadow to be an outer shadow
                .Format.Shadow.Transparency = 0.5 'make the shadow 50% transparent
            End With 'stop working with the existingChartObject.Chart.SeriesCollection(2)
    
            objExcel.Run("Macro2") 'Run Macro2 to delete the unecessary series
    
            'Now that we have a chart, we need to copy and paste it into the word document
            objExcel.ActiveChart.ChartArea.Select() 'Select the chart
            objExcel.ActiveChart.ChartArea.Copy() 'Copy it
            oDoc.Bookmarks.Item("bkNitrateChart").Range.Paste() 'Paste it at the bookmark where it's meant to go
    
            objSheet = Nothing 'Dispose of the worksheet
            objWrkBk.Close(SaveChanges:=False) 'Close the workbook without saving changes
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkBk) 'release the workbook
            objWrkBk = Nothing 'set the object to Nothing
            objExcel.Quit() 'Quit Excel
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel) 'release the Excel Application
            objExcel = Nothing 'set the object to Nothing
            GC.Collect() 'Collect any left over garbage (GC = Garbage Collect)
            'Suspend the current thread until the thread that is processing 
            'the queue of finalizers has emptied that queue.
            GC.WaitForPendingFinalizers()
            '
            '
            '---------------------------------------------------------Nitrate Graph End

    • Marked as answer by GrimstoneX Tuesday, September 22, 2009 7:22 PM
    Tuesday, September 22, 2009 7:21 PM

All replies

  • 1. Please post Excel programming issue to Discussions in Office Developer . That forum is mainly used to discuss Office Development issues.

    2. Whether you can record Macro in VBA when you manually format chart object. If you can, just manually convert VBA code to VB.NET code or directly call VBA Macro in automation client application to implement your requirement.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, May 14, 2009 2:43 AM
  • The following is what I eventually came up with:

     
     '---------------------------------------------------------Nitrate Graph
            '
            '
            'Build the nitrate graph
            Dim objExcel As New Excel.Application 'create an Excel application object
            Dim objWrkBk As Excel.Workbook 'create a workbook
            Dim objSheet As Excel.Worksheet 'create a worksheet
            Dim Range As Excel.Range 'create a range
            Dim Chart As Excel.Chart 'create a chart
            Dim chartObjects As Excel.ChartObjects = Nothing 'create a chartObjects instance
            Dim existingChartObject As Excel.ChartObject = Nothing 'create a chartObject instance
    
            objExcel = New Excel.Application 'start the Excel application
            objWrkBk = objExcel.Workbooks.Add 'add the workbook to this excel application
            objSheet = objWrkBk.Sheets.Add 'add the worksheet to the workbook object
            objSheet = objWrkBk.Sheets(1) 'objSheet is the first sheet in the workbook
            Chart = objExcel.Charts.Add 'add a chart to the Excel application
            'objExcel.Visible = True
    
            '''''''''''''''''''''''''''''''''''''''''''''
            'Create a macro to be run within the excel  '
            'application to delete a series that is     '
            'otherwise undeletable.                     '
            '''''''''''''''''''''''''''''''''''''''''''''
    
            Dim macro As VBIDE.VBComponent 'create a macro object
            Dim sCode As String 'create a string to hold the macro programming
    
    retry:  'A point at which the macro creation is retried if unsuccessful
    
            'Add in a macro so that Excel will delete the day as the series.
            Try
                macro = objWrkBk.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule) 'place the macro into the workbook
            Catch ex As Exception 'if an error occurs, catch it and do the following:
                'If the user has not enabled access to Visual Basic a Run-time error will occur.
                'Details on this error can be found here:  http://support.microsoft.com/kb/282830/en-us
                'Run-time error '6068': Programmatic Access to Visual Basic Project is not trusted 
                Dim Response As Integer
                'if the error is caught, a message box will display with the following text instructions on how to fix the problem.
                Response = MessageBox.Show("Access is denied from Microsoft Excel.  You need to do the following:" & vbCr & vbCr & _
                                "   1. Open Microsoft Office Excel 2007.  Click the Microsoft Office" & vbCr & _
                                "         button, and then click Excel Options." & vbCr & vbCr & _
                                "   2. Click the Trust Center tab, and then click Trust Center Settings." & vbCr & vbCr & _
                                "   3. Click the Macro Settings tab, click to select the Trust access to " & vbCr & _
                                "       the VBA project object model check box, and then click OK." & vbCr & vbCr & _
                                "   4. Click OK. " & vbCr & vbCr & _
                                "Restart IX Report Gen.", "Security Access Denied", MessageBoxButtons.RetryCancel, MessageBoxIcon.Exclamation)
                If Response = 4 Then 'If the user selected to retry they will be taken to the retry point above
                    GoTo retry
                Else
                    End 'If the user cancels the program simply ends.
                End If
            End Try
    
            'The macro code is put into the sCode string.
            'Line by line, the code below is described:
    
            'Sub Macro2()
            'ActiveSheet.ChartObjects(""Chart 1"").Activate     'Activate the chart in the sheet
            'ActiveChart.SeriesCollection(1).Select             'Select the chart's 1st data series
            'Selection.Delete                                   'Delete the chart's 1st data series
            'End Sub                                            'End the macro
    
            sCode = "Sub Macro2()" & vbCr & "ActiveSheet.ChartObjects(""Chart 1"").Activate" _
                    & vbCr & "ActiveChart.SeriesCollection(1).Select" _
                    & vbCr & "Selection.Delete" _
                    & vbCr & "End Sub"
            'The sCode string is placed into the empty macro
            macro.CodeModule.AddFromString(sCode)
    
    
            'Populate the Nitrate Avegare Array
            Dim HeadingArray(1, 1) As String 'A HeadingArray is created to place headings into the Excel sheet
            HeadingArray(0, 0) = "Day" 'Heading 1 is Day
            HeadingArray(0, 1) = "Nitrate Avg" 'Heading 2 is Nitrate Avg
            Range = objSheet.Range("A1", "B1") 'Range is assigned to the cells A1 and B1
            Range.Value = HeadingArray 'The value of the range is set to the value of the HeadingArray.  A1=Day and B1=Nitrate Avg
            'Range is now assigned to the cells from A2 to the B column and 'as many rows as are in the reporting month plus 1
            'to accomodate the heading row.
            Range = objSheet.Range("A2", "B" & (DaysInMonth + 1))
            Range.Value = DailyAverageArray 'The value of Range is now assigned the values of DailyAverageArray
            Range = objSheet.Range("A1", "B" & DaysInMonth + 1) 'Range is reassigned to select the whole edited area from A1 to B#
    
            'This is the title later used for the chart.  An example of what the title would
            'say if it were April is, Daily Nitrate Averages for April
            Dim Title As String = "Daily Nitrate Averages for " & Month
    
            Chart.Location(Excel.XlChartLocation.xlLocationAsObject, objSheet.Name) 'The chart is placed into the worksheet
            ' Get the ChartObjects collection for the sheet.
            chartObjects = objSheet.ChartObjects() 'chartObjects is assigned to this sheet
    
    
            ' Get the chart to modify.  This is the first item in chartObjects
            existingChartObject = chartObjects.Item(1)
    
            'Custom settings are assigned to the chart using the With keyword
            With existingChartObject
                .Chart.ChartType = Excel.XlChartType.xlLine 'This is a line graph
                .Chart.SetSourceData(Range, PlotBy:=Excel.XlRowCol.xlColumns) 'we plot by the columns selected--this gives us our headings as names
                .Chart.HasLegend = True 'give the chart a legend
                .Chart.HasTitle = True 'tell the chart it has a title
                .Chart.ChartTitle.Text = Title 'give the chart its title (declared and assigned earlier)
                .Shadow = True 'Give the chart a shadow effect
                .Chart.ChartArea.Shadow = True 'Give the chart area a shadow effect
                .Chart.ChartArea.Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'assign an outer shadow style
                .Chart.Legend.Position = Excel.XlLegendPosition.xlLegendPositionTop 'position the legend at the top of the chart
                .Chart.ChartArea.Format.Glow.Radius = 10 'add a glow with a radius of 10
                .Chart.ChartArea.Format.Glow.Color.RGB = RGB(90, 90, 90) 'set the color of the glow to a gray color
                .Chart.ChartArea.Format.Fill.Visible = True 'make the fill on the chart visible
                'make the fill a gradient that fades vertically from one color to the next with a Fog style.
                '.Chart.ChartArea.Format.Fill.PresetGradient(MsoGradientStyle.msoGradientVertical, 2, MsoPresetGradientType.msoGradientFog)
                'give the chart major gridlines on it's x axis
                .Chart.Axes(Excel.XlAxisGroup.xlPrimary).HasMajorGridlines = True
            End With 'Finish working with existingChartObject
    
            'from the perspective of the sheet, set the Width and Height to 500 X 300
            objSheet.ChartObjects(1).Width = 500
            objSheet.ChartObjects(1).Height = 300
    
            'Format the Chart's 2nd data series
            With existingChartObject.Chart.SeriesCollection(2)
                .Shadow = True 'give it a shadow
                .Format.Shadow.Style = MsoShadowStyle.msoShadowStyleOuterShadow 'set the shadow to be an outer shadow
                .Format.Shadow.Transparency = 0.5 'make the shadow 50% transparent
            End With 'stop working with the existingChartObject.Chart.SeriesCollection(2)
    
            objExcel.Run("Macro2") 'Run Macro2 to delete the unecessary series
    
            'Now that we have a chart, we need to copy and paste it into the word document
            objExcel.ActiveChart.ChartArea.Select() 'Select the chart
            objExcel.ActiveChart.ChartArea.Copy() 'Copy it
            oDoc.Bookmarks.Item("bkNitrateChart").Range.Paste() 'Paste it at the bookmark where it's meant to go
    
            objSheet = Nothing 'Dispose of the worksheet
            objWrkBk.Close(SaveChanges:=False) 'Close the workbook without saving changes
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objWrkBk) 'release the workbook
            objWrkBk = Nothing 'set the object to Nothing
            objExcel.Quit() 'Quit Excel
            System.Runtime.InteropServices.Marshal.ReleaseComObject(objExcel) 'release the Excel Application
            objExcel = Nothing 'set the object to Nothing
            GC.Collect() 'Collect any left over garbage (GC = Garbage Collect)
            'Suspend the current thread until the thread that is processing 
            'the queue of finalizers has emptied that queue.
            GC.WaitForPendingFinalizers()
            '
            '
            '---------------------------------------------------------Nitrate Graph End

    • Marked as answer by GrimstoneX Tuesday, September 22, 2009 7:22 PM
    Tuesday, September 22, 2009 7:21 PM