Creating a chart from dynamic excel range

Hello Everyone!

I am hoping someone can help me with the issues I am having with my code. I am trying to create a tool for my office to be able to pull information out of a report, and output the pertinent information into a DataGrid, and then take that information and display it on a chart. All of this is displayed on one form. Below are the problems I am running into with my code:

*** FYI all of my problems occur in the dataButton_click method***
#1 - I was previously able to open the workbook, have VB connect to it, pull the information from excel, and output it to the DataGrid. However, I must have changed something without realizing because now it throws an error at this line everytime I click the data button.

#2 - Is it possible to set VB so that it selects an entire row? The data I'm using does not always have the same number of rows of data, but the columns do not change. Is there a way to select a whole column?

#3 - I have a chart that I want to output the information from the DataGrid. Is there a way to connect either the information from excel, or the DataGrid to the chart so it outputs the information I pulled from excel?

Code:

---

Option Explicit On
Option Strict On
Imports Microsoft.Office.Interop
Imports System.Data.OleDb


Public Class Form1
    Private strFileName As String
    'Declare our Excel Application object and Workbook object
    Private moApp As Excel.Application
    Private moWB As Excel.Workbook

    Private Sub openButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles openButton.Click


        'Open a selected workbook (*.xls)
        Dim OpenDlg As New OpenFileDialog
        Using OpenDlg
            With OpenDlg
                .CheckFileExists = True
                .CheckPathExists = True
                .Filter = "ExcelWorkbooks Only (*.xls)|*.xls"
                .FilterIndex = 1
                .Multiselect = False
                .ShowHelp = False
                .ShowReadOnly = False
                .Title = "Select an Excel Workbook file"
                'If a file is selected then lets open it
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    strFileName = OpenDlg.FileName
                    'Call the .Open function and assign the return to the workbook variable for use
                    'in other functions/areas in our app
                    moWB = moApp.Workbooks.Open(strFileName)
                    'Show the opened workbook/application
                    moApp.Visible = True
                End If
            End With
        End Using


    End Sub

    Private Sub exitButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles exitButton.Click
        Me.Close()
    End Sub

    Private Sub dataButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataButton.Click
        Dim cnRange As String = "provider=Microsoft.Jet.OLEDB.4.0; " & _
            "data source='" & strFileName.ToString() & "';" & _
            "Extended Properties=""Excel 8.0; IMEX=1; HDR=No;"""

        Using MyConnection As New System.Data.OleDb.OleDbConnection(cnRange)
            MyConnection.Open()
            ' #1  ^this line shows this error: System.Data.OleDb.OleDbException was unhandled
            ' The Microsoft Jet database engine cannot open the file ''
            ' It is already opened exclusively by another user, or you need permission to view its data.
 

            Dim cmd As OleDbCommand = New OleDbCommand( _
                "SELECT F1 As Honda_P/N, F2 As C2, F3 As C3, F4 As C4, F5 As C5, F6 As C6 FROM [Sheet1$J4:N200]", MyConnection)
            ' #2  Is there anyway to select whole rows?  My data is not always the same amt of rows ^^^^
            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)

            DataGridView1.DataSource = dt
            Chart1.DataSource = dt
            ' #3  ^ is there anyway to link the chart to the data from excel?

        End Using

    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Error handling on creating an Excel application as the user may not have it installed
        Try
            moApp = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub Chart1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Chart1.Click

    End Sub
End Class

---