Use VBA to create Excel chart

[RERThird]

We need a program that uses VBA to enter data into an Excel sheet and then displays a line chart. Office version is 2013.

I found a number of examples on the Internet, but could not get any of them to work properly. This may be due in part to differences in the many versions of Excel.

The following is one of multiple programs that I have tried without success:

Set objXL = CreateObject("Excel.application")
Set objBook = objXL.Workbooks.Add
'Set a reference to Sheet1
Set objSheet = objBook.Worksheets.Item(1)

Charts.Add


'Set the dummy data range for the chart

ActiveChart.SetSourceData Sheets("Sheet1").Range("a1:d4"), PlotBy:=xlColumns
ActiveChart.ChartType = xlLineMarkers


'Manually set the values for the data series
ActiveChart.SeriesCollection(1).Formula = "=SERIES(""First Data"",{""a"",""b"",""c"",""d""},{2,3,4,5},1)"
ActiveChart.SeriesCollection(2).Formula = "=SERIES(""Second Data-"",{""a"",""b"",""c"",""d""},{6,7,8,9},2)"
ActiveChart.SeriesCollection(3).Formula = "=SERIES(""Third Data"",{""a"",""b"",""c"",""d""},{10,11,12,13},3)"



' Make Excel Visible:
objXL.Visible = True
objXL.UserControl = True




Thank you very much for your assistance.

 

[RERThird]

The sheet and graph are correct, but entry of a new chart title fails.


On Error GoTo ErrorHandler


Dim objExcel As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet
Dim objShape As Shape




Dim strFile As String


strFile = "D:\ExcelWorkbooks\ExcelWorkBook.xlsx"


' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True


'Opens up the Workbook
Set WB = objExcel.Workbooks.Open(strFile)


'Sets the Workseet to the last active sheet - Better to use the commented version and use the name of the sheet.
Set WS = WB.ActiveSheet


WS.Cells(2, 1) = "01/01/2015"
WS.Cells(2, 2) = "90"
WS.Cells(3, 1) = "02/01/2015"
WS.Cells(3, 2) = "85"
WS.Cells(4, 1) = "03/01/2015"
WS.Cells(4, 2) = "100"
WS.Cells(5, 1) = "04/01/2015"
WS.Cells(5, 2) = "80"
WS.Cells(6, 1) = "05/01/2015"
WS.Cells(6, 2) = "75"


ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$2:$B$6")
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveChart.HasTitle = False
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Text = "Testing"
Selection.Format.TextFrame2.TextRange.Characters.Text = "Testing"
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 7).Font
.BaselineOffset = 0
.Bold = msoFalse
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(89, 89, 89)
.Fill.Transparency = 0
.Fill.Solid
.Size = 14
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Spacing = 0
.Strike = msoNoStrike
End With


ActiveChart.Delete
WS.Delete
WB.Close
objExcel.Quit
Exit Sub

ErrorHandler:
'MsgBoxReturn = MsgBox("Error Source: Excel Sheet/Chart" & vbCrLf & "Error Line: " & Erl & vbCrLf & "Error Number: " & Err.Number & vbCrLf & Err.Description, vbCritical, "")
Err.Clear
'objExcel.Quit.Quit SaveChanges:=wdDoNotSaveChanges
'objExcel.Quit