Visual studio 2008 not showing chart from excel file

Question

Hello there,

I am trying to show my excel chart in visual basic listbox and it does not work.

I have added the reference of microsoft.office.interop.excel

in my code i have put the range of column "A" and i do not know how to change the range to where the chart is in the excel.

Here is my code!

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim objXLApp As Excel.Application
        Dim intLoopCounter As Integer
        Dim objXLWb As Excel.Workbook
        Dim objXLWs As Excel.Worksheet
        Dim objRange As Excel.Range
        objXLApp = New Excel.Application
        objXLApp.Workbooks.Open("*****\Book1.xlsx")
        objXLWb = objXLApp.Workbooks(1)
        objXLWs = objXLWb.Worksheets(1)
        For intLoopCounter = 1 To CInt(objXLWs.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row)
            objRange = objXLWs.Range("A" & intLoopCounter)
            ListBox1.Items.Add(objRange.Value)
        Next intLoopCounter
        objXLApp.Quit()
    End Sub
End Class

I have attached the excel file screen shot if that can help in any way...So basically When i click on the button in VB than it should show me the excel chart. My code just shows me the column "A" which shows me the "data" column only from the excel file. I will be very thankful to any kind of help you guys can provide me with. Thanks in ADVANCE.

---

Amman Anwar

Answer 1

Hi Amman,

Welcome to the MSDN forum.

To display multiple lines data, I’d suggest you use DataGridView. You can simply bind the worksheet to DGV like this:

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

        Try

            If OpenFileDialog1.ShowDialog() = DialogResult.OK Then

                Dim myconn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & OpenFileDialog1.FileName & ";Extended Properties=Excel 12.0 Xml;"

                Dim MyConnection As New System.Data.OleDb.OleDbConnection(myconn)

                Dim MyCommand As New System.Data.OleDb.OleDbCommand("select * from [Sheet1$]", MyConnection)

                Dim MyAdapter As New System.Data.OleDb.OleDbDataAdapter

                MyAdapter.SelectCommand = MyCommand

                Dim DtSet As New System.Data.DataSet()

                MyAdapter.Fill(DtSet)

                DataGridView1.Show()

                DataGridView1.DataSource = DtSet.Tables(0)

                MyConnection.Close()

            End If

        Catch ex As Exception

            MsgBox(ex.ToString)

        End Try

End Sub

Moreover, you can use Chart.Export Method to convert the chart to image and then display it.

I hope this will be helpful.

Best regards,

---

Shanks Zen
MSDN Community Support | Feedback to us

Answer 2

Do I need to add any reference?

---

Amman Anwar|MCTS|MOS|

Answer 3

You just need to reference to microsoft.office.interop.excel.

---

Shanks Zen
MSDN Community Support | Feedback to us