Delete and Replace All Embedded Charts in Workbook

Thu, 10 Dec 2009 12:57:01 -0700

I have written a VBA code that generates several charts but what I'd like to do is that each time I run the program the new chart comes and overwrites the old one, so I will always have the most accurate chart in my workbook. Also, When you'll see my code, I have included in every 'Phase' what I thought to be my solution to my problem in deleting and replacing each and every chart in the workbook:
Of course I customized the loop below for each chart
-----------------------------------------

For Each SumExpC In ThisWorkbook.Charts

If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")
------------------------------------------

Thank you so much for help. I will really appreciate any insight.
PS: the code is extremely redundant but it's important that I attach it all, So you can see all my embedded charts.

And Sorry for this long email.;)

'Phase 2 ---------------------------------------------------------
Sub SampleTransparencyReport()

Dim SumExpC As Chart 'Summary Exposure by Currency

'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each SumExpC In ThisWorkbook.Charts

If SumExpC.Name = "SumExpC" Then
SumExpC.Delete
End If
Next
Set SumExpC = ThisWorkbook.Charts.Add
Set SumExpC = SumExpC.Location(where:=xlLocationAsObject, Name:="Exposure I")

With SumExpC
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("P32:Q39"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Currency" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With SumExpC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SumExpC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SumExpC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 384 'Range("A31").Top
.Left = 0 ' Range("A31").Left
.Height = 180
.Width = 280
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 3 ---------------------------------------------------------
Dim SumExpA As Chart 'Summary Exposure by Asset Class

'ActiveSheet.ChartObjects.Delete

For Each SumExpA In ThisWorkbook.Charts

If SumExpA.Name = "SumExpA" Then
SumExpA.Delete
End If
Next
Set SumExpA = ThisWorkbook.Charts.Add
Set SumExpA = SumExpA.Location(where:=xlLocationAsObject, Name:="Exposure I")

With SumExpA
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure I").Range("M32:N40"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Summary Exposure by Asset Class" 'Chart Title
.ChartTitle.Font.Size = 9.6 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With SumExpA.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SumExpA.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SumExpA.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 384 'Range("E31").Top
.Left = 306 'Range("E31").Left
.Height = 180
.Width = 280
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With

'Phase 4 ---------------------------------------------------------
'This MsgBox returns values for the Height, Width, Top & Left & Cell positions of a Chart in a Worksheet
' Dim SumExp As ChartObject
' Dim SumExpTop As Integer
' Dim SumExpLeft As Integer
' Dim SumExpTopLeftCell As String
'
' For Each SumExp In ActiveSheet.ChartObjects
' SumExpTop = SumExp.Top
' SumExpLeft = SumExp.Left
' SumExpWidth = SumExp.Width 'delete
' SumExpHeight = SumExp.Height 'delete
' SumExpTopLeftCell = SumExp.TopLeftCell.Address
' MsgBox (SumExp.Name & vbCr & "Width=" & SumExpWidth & vbCr & "Height=" & SumExpHeight & vbCr & "Top = " & SumExpTop & vbCr & "Left = " & SumExpLeft & vbCr & "Cell = " & SumExpTopLeftCell)
' Next

'Phase 5 ---------------------------------------------------------
Dim SectorBreakdownE As Chart 'Equity Sector Breakdown Exposures

'ActiveSheet.ChartObjects.Delete

For Each SectorBreakdownE In ThisWorkbook.Charts

If SectorBreakdownE.Name = "SectorBreakdownE" Then
SectorBreakdownE.Delete
End If
Next
Set SectorBreakdownE = ThisWorkbook.Charts.Add
Set SectorBreakdownE = SectorBreakdownE.Location(where:=xlLocationAsObjec t, Name:="Exposure II")

With SectorBreakdownE
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("P6:Q15"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With SectorBreakdownE.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SectorBreakdownE.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SectorBreakdownE.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 86 'Range("A7").Top
.Left = 0 ' Range("A7").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 6 ---------------------------------------------------------
Dim MarketCap As Chart 'Market Capitalization

'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each MarketCap In ThisWorkbook.Charts

If MarketCap.Name = "MarketCap" Then
MarketCap.Delete
End If
Next
Set MarketCap = ThisWorkbook.Charts.Add
Set MarketCap = MarketCap.Location(where:=xlLocationAsObject, Name:="Exposure II")

With MarketCap
.ChartType = xlBarClustered
.SetSourceData Source:=Sheets("Exposure II").Range("S6:T9"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Market Capitalization" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With MarketCap.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With MarketCap.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With MarketCap.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 86 'Range("D7").Top
.Left = 250 ' Range("D7").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 7 ---------------------------------------------------------
Dim LiquidP As Chart 'Liquidity Profile

'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each LiquidP In ThisWorkbook.Charts

If LiquidP.Name = "LiquidP" Then
LiquidP.Delete
End If
Next
Set LiquidP = ThisWorkbook.Charts.Add
Set LiquidP = LiquidP.Location(where:=xlLocationAsObject, Name:="Exposure II")

With LiquidP
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V6:W10"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Liquidity Profile" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With LiquidP.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With LiquidP.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With LiquidP.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 86 'Range("I7").Top
.Left = 490 ' Range("I7").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 8 ---------------------------------------------------------
Dim SectorBreakdownC As Chart 'Credit Sector Breakdown

'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each SectorBreakdownC In ThisWorkbook.Charts

If SectorBreakdownC.Name = "SectorBreakdownC" Then
SectorBreakdownC.Delete
End If
Next
Set SectorBreakdownC = ThisWorkbook.Charts.Add
Set SectorBreakdownC = SectorBreakdownC.Location(where:=xlLocationAsObjec t, Name:="Exposure II")

With SectorBreakdownC
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("P27:Q36"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Sector Breakdown" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With SectorBreakdownC.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SectorBreakdownC.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With SectorBreakdownC.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 330 'Range("A27").Top
.Left = 0 ' Range("A27").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 9 ---------------------------------------------------------
Dim RatingDistrib As Chart 'Credit Ratings Distribution

'ActiveSheet.ChartObjects.Delete '.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each RatingDistrib In ThisWorkbook.Charts 'Replacing Old Chart by New One

If RatingDistrib.Name = "RatingDistrib" Then
RatingDistrib.Delete
End If
Next
Set RatingDistrib = ThisWorkbook.Charts.Add
Set RatingDistrib = RatingDistrib.Location(where:=xlLocationAsObject, Name:="Exposure II")

With RatingDistrib
.ChartType = xlBarClustered 'Clustered Bar Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("S27:T34"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "Ratings Distribution" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With RatingDistrib.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With RatingDistrib.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With RatingDistrib.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 330 'Range("E27").Top
.Left = 250 ' Range("E27").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 10 ---------------------------------------------------------
Dim DV01 As Chart 'DV01 by Maturity Bucket

'ActiveSheet.ChartObjects.Delete 'Add .Delete if you want to CLEAR ALL EXISTING CHARTS

For Each DV01 In ThisWorkbook.Charts 'Replacing Old Chart by New One

If DV01.Name = "DV01" Then
DV01.Delete
End If
Next
Set DV01 = ThisWorkbook.Charts.Add
Set DV01 = DV01.Location(where:=xlLocationAsObject, Name:="Exposure II")

With DV01
.ChartType = xlColumnClustered 'Clustered Column Chart Type
.SetSourceData Source:=Sheets("Exposure II").Range("V27:W30"), PlotBy:=xlColumns
.HasTitle = True
.HasLegend = False 'No Legend
.HasAxis(xlValue, xlPrimary) = True 'Has Horizontal Axis
.HasAxis(xlCategory, xlPrimary) = True 'Has Vertical Axis
.ChartTitle.Text = "DV01 by Maturity Bucket" 'Chart Title
.ChartTitle.Font.Size = 8 'Font Size
.ChartTitle.Font.Name = "Bell MT" 'Font
.SeriesCollection(1).Interior.ColorIndex = 9 'Dark Red Color for Bars
.PlotArea.Border.LineStyle = xlContinuous
.ChartArea.Border.LineStyle = 0 'Removes the Chart Area Border Line

With DV01.Axes(xlValue, xlPrimary) 'Horizontal Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With DV01.Axes(xlCategory, xlPrimary) 'Vertical Label Font Size and Font Name
.TickLabels.Font.Size = 8
.TickLabels.Font.Name = "Bell MT"
End With

With DV01.PlotArea
.Border.Color = RGB(127, 127, 127) 'Chart Border Line Custom "Grey" Color
End With

With .Parent
.Top = 330 'Range("I27").Top
.Left = 490 ' Range("I27").Left
.Height = 186
.Width = 240
End With

With ActiveChart.Axes(xlCategory) 'Vertical Axis
.HasMajorGridlines = False
.MajorTickMark = xlNone
End With

With ActiveChart.Axes(xlValue) 'Horizontal Axis
.HasMajorGridlines = True
.MajorTickMark = xlNone
End With
End With

'Phase 11 ---------------------------------------------------------

End With
End Sub

'Delete and Replace All Embedded Charts in Workbook' Thread RSS Feed

Delete and Replace All Embedded Charts in Workbook