Styjun

I'm fairly new to Visual Basic. I'm using the Visual Studio 2013 and MS Excel 2010. I would like to program a code with VB that can retrieve information from the Excel .xlsx file and using that information to make charts.

Here's the edited version:

Imports System.Reflection
Imports Excel = Microsoft.Office.Interop.Excel
'Add reference Assemblies, Framework, System.Windows.Forms.DataVisualization
'Imports System.Windows.Forms.DataVisualization.Charting


Public Class Form1

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

 Dim excelApp As Excel.Application
 Dim excelWB As Excel.Workbook
 Dim excelWS As Excel.Worksheet
 Dim FNameRng As Excel.Range
 Dim AveRng As Excel.Range
 Dim AveCLRng As Excel.Range
 Dim AveUCLRng As Excel.Range
 Dim FNameArry As New ArrayList()
 Dim AveArry As New ArrayList()
 Dim AveCLArry As New ArrayList()
 Dim AveUCLArry As New ArrayList()

 excelApp = CreateObject("Excel.Application")
 excelApp.Visible = False
 'Open the Workbook
 excelWB = excelApp.Workbooks.Open("C:UsersJoesphDocumentsChartsControl Limit18x17 - 10 mil stop.xlsx")
 excelWS = excelApp.Sheets("18x17 - 10 mil stop")

 'Set the Range for File Name
 FNameRng = excelWS.Range("A2", excelWS.Range("A2").End(Excel.XlDirection.xlDown))
 'Set the Range for Average Data
 AveRng = excelWS.Range("B2", excelWS.Range("B2").End(Excel.XlDirection.xlDown))
 AveCLRng = excelWS.Range("H2", excelWS.Range("H2").End(Excel.XlDirection.xlDown))
 AveUCLRng = excelWS.Range("I2", excelWS.Range("I2").End(Excel.XlDirection.xlDown))

 'Store Range as Array
 FNameArry.Add(FNameRng.Value)
 AveArry.Add(AveRng.Value)
 AveCLArry.Add(AveCLRng.Value)
 AveUCLArry.Add(AveUCLRng.Value)

 Me.CenterToScreen()
 Me.WindowState = FormWindowState.Maximized

 Chart1.Titles.Add("Title1")
 Chart1.Titles(0).Text = "Average"
 Chart1.Titles(0).Font = New Font("Garamond", 24, FontStyle.Bold)

 Chart1.Series("Series1").XValueMember = "FNameArry"
 Chart1.Series("Series1").YValueMembers = "AveArry"
 Chart1.Series("Series1").YValueMembers = "AveCLArry"
 Chart1.Series("Series1").YValueMembers = "AveUCLArry"


End Sub
End Class

So, I store the Excel range into an arraylist. I used the array as the Chart points. The program now can run without any error, but it display nothing other than the chart title. What did I do wrong here? Do I have to loop the array for the chart to display the X and Y axis? Any help would be appreciated. Thank you!

Here it is. I am using the OLE db driver to get data out of xlsx instead of Interop. I am also using 3 series instead of a single one with multiple Y values.

Imports System.Windows.Forms.DataVisualization.Charting
Imports System.Data
Imports System.Data.OleDb

'The Excel file name
Dim fileName As String = "YourExcelData.xlsx"

'connection string for Xlsx files - Microsoft ACE OLEDB 12.0
'Connect to Excel 2007 (and later) files with the Xlsx file extension. 
'That is the Office Open XML format with macros disabled.
' "HDR=Yes;" indicates that the first row contains columnnames, not data. 
'"HDR=No;" indicates the opposite.

'"+fileNameString+" remove String from it as defind above
Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+fileNameString+";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Dim myConnection As New OleDbConnection(sConn)
myConnection.Open()

' The code to follow uses a SQL SELECT command to display the data from the worksheet.
' Create new OleDbCommand to return data from worksheet.
' change range 
Dim myCommand As New OleDbCommand("Select * From [data1$A2:I2500]", myConnection)

' create a database reader 
Dim myReader As OleDbDataReader = myCommand.ExecuteReader (CommandBehavior.CloseConnection)
' Populate the chart with data in the file
' can also use Chart.DataBindTable
Chart1.Series(0).Points.DataBindXY(myReader, "FNameArry", myReader, "AveArry")
Chart1.Series(1).Points.DataBindXY(myReader, "FNameArry", myReader, "AveCLArry")
Chart1.Series(2).Points.DataBindXY(myReader, "FNameArry", myReader, "AveUCLArry")

' close the reader and the connection
myReader.Close()
myConnection.Close()