Help with disappearing chart as a result of macro

TwoAce

Board Regular
Joined
Feb 20, 2008
Messages
152
Office Version
  1. 365
Platform
  1. Windows
Hi all,

There are charts in two tabs in my worksheet. One worksheet has a button with a macro with the below.
When I run the macro, the charts in my other tab get deleted as well. I want them to go unharmed. I hope one of you knows how to do this. I got this macro from another spreadsheet and didn't write the code myself, I highlighted the part that I think needs to be changed in red, but I'm not sure since I'm a novice. Thanks.

Best regards,
Rob


Option Explicit

Sub GetData()
Dim DataSheet As Worksheet
Dim endDate As String
Dim startDate As String
Dim fromCurr As String
Dim toCurr As String
Dim str As String
Dim LastRow As Integer
Dim bam As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("Data Currency").Cells.Clear

Set DataSheet = ActiveSheet

startDate = DataSheet.Range("startDate").Value
endDate = DataSheet.Range("endDate").Value
fromCurr = DataSheet.Range("fromCurr").Value
toCurr = DataSheet.Range("toCurr").Value

If DataSheet.Range("bam") = "b" Then
bam = "bid"
ElseIf DataSheet.Range("bam") = "a" Then
bam = "ask"
ElseIf DataSheet.Range("bam") = "m" Then
bam = "mid"
End If

str = "http://www.oanda.com/currency/historical-rates/download?quote_currency=" _
& fromCurr _
& "&end_date=" _
& Year(endDate) & "-" & Month(endDate) & "-" & Day(endDate) _
& "&start_date=" _
& Year(startDate) & "-" & Month(startDate) & "-" & Day(startDate) _
& "&period=daily&display=absolute&rate=0&data_range=c&price=" & bam & "&view=table&base_currency_0=" _
& toCurr _
& "&base_currency_1=&base_currency_2=&base_currency_3=&base_currency_4=&download=csv"

QueryQuote:
With Sheets("Data Currency").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data Currency").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Sheets("Data Currency").Range("a5").CurrentRegion.TextToColumns Destination:=Sheets("Data Currency").Range("a5"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)

Sheets("Data Currency").Columns("A:B").ColumnWidth = 12
Sheets("Data Currency").Range("A1:b2").Clear

LastRow = Sheets("Data Currency").UsedRange.Row - 6 + Sheets("Data Currency").UsedRange.Rows.Count

Sheets("Data Currency").Range("A" & LastRow + 2 & ":b" & LastRow + 5).Clear

Sheets("Data Currency").Sort.SortFields.Add Key:=Range("A5:A" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Data Currency").Sort
.SetRange Range("A5:b" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
.SortFields.Clear
End With

DeleteCharts

Application.DisplayAlerts = True


With ActiveSheet.ChartObjects.Add _
(Left:=Range("F2").Left, Width:=375, Top:=Range("F2").Top, Height:=200)
.Chart.SetSourceData Source:=Sheets("Data Currency").Range("A5:b" & LastRow)
.Chart.ChartType = xlLine
End With

Dim ch As ChartObject
For Each ch In ActiveSheet.ChartObjects
ch.Select
ActiveChart.Axes(xlValue).MinimumScale = WorksheetFunction.Min(Sheets("Data Currency").Range("b5:b" & LastRow))
ActiveChart.Axes(xlValue).MaximumScale = WorksheetFunction.Max(Sheets("Data Currency").Range("b5:b" & LastRow))
ActiveChart.Legend.Select
Selection.Delete
Next ch

End Sub
Sub DeleteCharts()
On Error GoTo ExitChart
Dim ws As Worksheet
Dim chObj As ChartObject
Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
For Each chObj In ws.ChartObjects
chObj.Delete
Next chObj
Next ws

ActiveWorkbook.Charts.Delete

ExitChart:
Application.DisplayAlerts = True
Exit Sub
End Sub
 
Last edited:

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Fixed it by deleting everything connected to making the graph and deleting it. Now I get just the output data from Oanda.
See the solution below:

Option Explicit


Sub GetData()
Dim DataSheet As Worksheet
Dim endDate As String
Dim startDate As String
Dim fromCurr As String
Dim toCurr As String
Dim str As String
Dim LastRow As Integer
Dim bam As String


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual


Sheets("Data Currency").Cells.Clear


Set DataSheet = ActiveSheet


startDate = DataSheet.Range("startDate").Value
endDate = DataSheet.Range("endDate").Value
fromCurr = DataSheet.Range("fromCurr").Value
toCurr = DataSheet.Range("toCurr").Value


If DataSheet.Range("bam") = "b" Then
bam = "bid"
ElseIf DataSheet.Range("bam") = "a" Then
bam = "ask"
ElseIf DataSheet.Range("bam") = "m" Then
bam = "mid"
End If


str = "http://www.oanda.com/currency/historical-rates/download?quote_currency=" _
& fromCurr _
& "&end_date=" _
& Year(endDate) & "-" & Month(endDate) & "-" & Day(endDate) _
& "&start_date=" _
& Year(startDate) & "-" & Month(startDate) & "-" & Day(startDate) _
& "&period=daily&display=absolute&rate=0&data_range=c&price=" & bam & "&view=table&base_currency_0=" _
& toCurr _
& "&base_currency_1=&base_currency_2=&base_currency_3=&base_currency_4=&download=csv"


QueryQuote:
With Sheets("Data Currency").QueryTables.Add(Connection:="URL;" & str, Destination:=Sheets("Data Currency").Range("a1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With


Sheets("Data Currency").Range("a5").CurrentRegion.TextToColumns Destination:=Sheets("Data Currency").Range("a5"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, other:=True, OtherChar:=",", FieldInfo:=Array(1, 2)


Sheets("Data Currency").Columns("A:B").ColumnWidth = 12
Sheets("Data Currency").Range("A1:b2").Clear


LastRow = Sheets("Data Currency").UsedRange.Row - 6 + Sheets("Data Currency").UsedRange.Rows.Count


Sheets("Data Currency").Range("A" & LastRow + 2 & ":b" & LastRow + 5).Clear


Sheets("Data Currency").Sort.SortFields.Add Key:=Range("A5:A" & LastRow), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With Sheets("Data Currency").Sort
.SetRange Range("A5:b" & LastRow)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
.SortFields.Clear
End With




End Sub
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top