Sub tests()
' tests Macro
' Macro recorded 14.10.2010 by Amilien 19.10.2010
Dim c As ChartObject
Dim calc As Range, y As Range, y1 As Range
'Calculate the number of values in a column
Set calc = Range("A:A").Find("not implemented", , xlValues, xlWhole, , , False)
'Y-axis selection
If Not calc Is Nothing Then
Set y_choice = Application.InputBox("Choose the Y axis column (address title's cell)", Type:=8)
'Calculate the number of values in a column
Set calc = Range("A:A").Find("not implemented", , xlValues, xlWhole, , , False)
Set y = Range(y_choice.Offset(1, 0), Cells(calc.Row - 1, y_choice.Column))
y.Select
For Each v In y
v.Value = v.Value * 1 'String to number
Next
y.Name = "y": y_choice.Name = "y_choice"
End If
If Not calc Is Nothing Then
Set y1_choice = Application.InputBox("Choose the Y1 axis column (address title's cell)", Type:=8)
Set y1 = Range(y1_choice.Offset(1, 0), Cells(calc.Row - 1, y1_choice.Column))
y.Select
For Each v In y1
v.Value = v.Value * 1 'String to number
Next
y1.Name = "yy": y1_choice.Name = "y1_choice"
End If
'X-axis selection
If Not calc Is Nothing Then
Set x_choice = Application.InputBox("Choose the X axis column (address title's cell)", Type:=8)
Set x = Range(x_choice.Offset(1, 0), Cells(calc.Row - 1, x_choice.Column))
x.Select
For Each v In x
v.Value = v.Value * 1 'String to number
Next
x.Name = "x": x_choice.Name = "x_choice"
End If
With Sheets("Sheet1")
Set c = .ChartObjects.Add(.Range("G15").Left, .Range("G15").Top, 800, 400)
End With
' Chart configurations
With c.Chart
.ChartType = xlLineMarkers
.SetSourceData Source:=Sheets("TX_WLAN_11n_framed_802.11n_HT40").Range("y,yy"), PlotBy:=xlColumns
.SeriesCollection(1).XValues = "=TX_WLAN_11n_framed_802.11n_HT40!x"
.SeriesCollection(1).Name = "=TX_WLAN_11n_framed_802.11n_HT40!y_choice"
.SeriesCollection(1).Name = "=TX_WLAN_11n_framed_802.11n_HT40!x_choice"
.HasTitle = True
.ChartTitle.Characters.Text = "XY Graph"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Range("y_choice")
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("x_choice")
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = Range("y1_choice")
.Legend.Position = xlBottom
.Legend.Font.size = 8
.Legend.Font.Bold = True
.ChartTitle.Text = "XY Graph"
.ChartTitle.Font.size = 8
.ChartTitle.Font.Bold = True
.Axes(xlCategory).TickLabels.Font.size = 8
.Axes(xlValue).TickLabels.Font.size = 8
' Add a sheet and open an HTML report
Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;file:///C:/_U1/PROGRAMME/NI_8.5/PROGRAMME_LV85/Report/TX_WLAN_11n_framed_802.11n_HT40_MCS7_25°C_Chip_Supply_3,850 V_PA_Supply_3,850 V_EVM_LIST_PWR_SWEEP_PA_1WATT_TEST_NO_1_ACRFreq_2417.000MHz.html", Destination:=Range("A1"))
.Name = "HTML_TEST(1)"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
End With
End Sub