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