I have a Start form with a "View Records" button. On the Records Form Load event I have the loop that populates a datagridview on the Records Form. What I want to do is show a progress bar next to the "View Records" button on the Start form that shows the progress of the datagridview on the Records form when the user clicks the "View Records" button. Then, once the datagridview loop is successfully completed I want to bring up the Records form (but still leave Start form open as parent form, so the "View Record" form would be brought up by a ShowDialog). I have the simple code to show the progress bar on "View Records" button click. I'm reading around to find that maybe a background worker might be what I need, but I do not know how to work with it. Could someone help walk me through it and provide some code to help me along? Some info, the start form is called 'Start' and the View Records form is called 'Records.' The progress bar is name 'pb'. Thank you in advance to anyone who attempts to help!
Ok, here's the updated code with the error
Imports Office = Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Start
Dim Records As New Records
Dim excel_app As Excel.Application
Dim workbook As Excel.Workbook
Dim sheet_name As String
Dim sheet As Excel.Worksheet
Dim exeDir As New IO.FileInfo(Reflection.Assembly.GetExecutingAssembly.FullName)
Dim xlPath = IO.Path.Combine(exeDir.DirectoryName, "Records.xlsx")
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
bw.RunWorkerAsync()
End Sub
Private Sub bw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bw.DoWork
' Get the Excel application object.
excel_app = New Excel.Application
' Make Excel visible (optional).
excel_app.Visible = False
' Open the workbook.
workbook = excel_app.Workbooks.Open(xlPath)
sheet_name = "2013"
sheet = excel_app.Worksheets("2013")
Dim ColumnCount, RowCount, TotalCellCount As Long
ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
Records.DataGridView1.ColumnCount = ColumnCount - 1
Records.DataGridView1.RowCount = RowCount - 1
Records.DataGridView1.ColumnHeadersVisible = True
Records.DataGridView1.RowHeadersVisible = True
TotalCellCount = Records.DataGridView1.ColumnCount * Records.DataGridView1.RowCount
pb.Visible = True
pb.Minimum = 0
pb.Value = 0
pb.Maximum = TotalCellCount
Records.DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
Records.DataGridView1.AllowUserToResizeColumns = False
Records.DataGridView1.AllowUserToResizeRows = False
Records.DataGridView1.ReadOnly = True
Records.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
'Loop through each column
Dim cIndex As Integer = 0
While cIndex < ColumnCount
'Loop through and populate each row in column
Dim rIndex As Integer = 0
While rIndex < RowCount - 1
If cIndex = 0 Then
'Set row header titles
Records.DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
If cIndex > 0 Then
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
'Set column header title
Records.DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
'Change last cell (Result) color Red or Green to represent positive gain or negative loss
If rIndex = RowCount - 2 Then
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
End If
End If
'Update the progress bar after each cell is populated
bw.ReportProgress((rIndex * cIndex) / TotalCellCount)
rIndex = rIndex + 1
End While
'Format all cells in column as currency values
Records.DataGridView1.Columns(cIndex).DefaultCellStyle.Format = "c"
'Make column unsortable
Records.DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
'Resize all Row Headers so user can see Row Titles without resizing
Records.DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
cIndex = cIndex + 1
End While
Records.DataGridView1.AutoResizeColumns()
End Sub
Private Sub bw_ProgressChanged(ByVal sender As Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bw.ProgressChanged
pb.Value = e.ProgressPercentage
End Sub
Private Sub bw_RunWorkerCompleted(ByVal sender As Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
If e.Error IsNot Nothing Then
MessageBox.Show(e.Error.Message, "Background Worker Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
If e.Cancelled Then
'worker was cancelled
Else
'worker completed, open form2 here
pb.Visible = False
Records.ShowDialog()
If (Records.DialogResult) Then
' Close the workbook.
workbook.Close()
' Close the Excel server.
excel_app.Quit()
End If
End If
End If
End Sub
Am I doing this right? And how do I fixed the error?
I would use a background worker on Form1 and then subscribe to its ProgressChanged event to update the progress bar. Then when the worker completes, you can pass that data to Form2 and open it.
Assuming you add a BackgroundWorker called bw to Form1
On the Button click event you need to start the worker by calling bw.RunWorkerAsync
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Setup()
bw.RunWorkerAsync()
End Sub
Private Sub Setup()
' Get the Excel application object.
excel_app = New Excel.Application
' Make Excel visible (optional).
excel_app.Visible = False
' Open the workbook.
workbook = excel_app.Workbooks.Open(xlPath)
sheet_name = "2013"
sheet = excel_app.Worksheets("2013")
Dim ColumnCount, RowCount, TotalCellCount As Long
ColumnCount = sheet.Range("A1").CurrentRegion.Columns.Count
RowCount = sheet.Range("A1").CurrentRegion.Rows.Count
Records.DataGridView1.ColumnCount = ColumnCount - 1
Records.DataGridView1.RowCount = RowCount - 1
Records.DataGridView1.ColumnHeadersVisible = True
Records.DataGridView1.RowHeadersVisible = True
TotalCellCount = Records.DataGridView1.ColumnCount * Records.DataGridView1.RowCount
pb.Visible = True
pb.Minimum = 0
pb.Value = 0
pb.Maximum = TotalCellCount
Records.DataGridView1.RowHeadersWidthSizeMode = DataGridViewRowHeadersWidthSizeMode.DisableResizing
Records.DataGridView1.AllowUserToResizeColumns = False
Records.DataGridView1.AllowUserToResizeRows = False
Records.DataGridView1.ReadOnly = True
Records.DataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
End Sub
Then in the bw.DoWork event you would run the code to fetch the data. Create a form level variable so you can access it in the DoWork and RunWorkerCompleted Events
Dim f as New RecordForm
Private Sub bw_DoWork(sender As System.Object, e As System.ComponentModel.DoWorkEventArgs) Handles bw.DoWork
'Loop through each column
Dim cIndex As Integer = 0
While cIndex < ColumnCount
'Loop through and populate each row in column
Dim rIndex As Integer = 0
While rIndex < RowCount - 1
If cIndex = 0 Then
'Set row header titles
Records.DataGridView1.Rows.Item(rIndex).HeaderCell.Value = sheet.Range("A1").Offset(rIndex + 1, cIndex).Value()
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
If cIndex > 0 Then
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = sheet.Range("A1").Offset(rIndex + 1, cIndex + 1).Value()
End If
'Set column header title
Records.DataGridView1.Columns(cIndex).HeaderText = sheet.Range("A1").Offset(0, cIndex + 1).Value
'Change last cell (Result) color Red or Green to represent positive gain or negative loss
If rIndex = RowCount - 2 Then
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value < 0 Then
Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Red
Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value > 0 Then
Records.DataGridView1.Item(cIndex, rIndex).Style.BackColor = Color.Green
Records.DataGridView1.Item(cIndex, rIndex).Style.ForeColor = Color.White
End If
If Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = 0 Then
Records.DataGridView1.Rows(rIndex).Cells(cIndex).Value = "Broke Even"
End If
End If
'Update the progress bar after each cell is populated
bw.ReportProgress((rIndex * cIndex) / TotalCellCount)
rIndex = rIndex + 1
End While
'Format all cells in column as currency values
Records.DataGridView1.Columns(cIndex).DefaultCellStyle.Format = "c"
'Make column unsortable
Records.DataGridView1.Columns(cIndex).SortMode = DataGridViewColumnSortMode.NotSortable
'Resize all Row Headers so user can see Row Titles without resizing
Records.DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
cIndex = cIndex + 1
End While
Records.DataGridView1.AutoResizeColumns()
End Sub
Private Sub bw_ProgressChanged(sender As Object, e As System.ComponentModel.ProgressChangedEventArgs) Handles bw.ProgressChanged
ProgressBar.Value = e.ProgressPercentage
End Sub
When the worker is finished it will fire its completed event.
Private Sub bw_RunWorkerCompleted(sender As Object, e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bw.RunWorkerCompleted
If e.Error IsNot Nothing Then
MessageBox.Show(e.Error.Message, "Background Worker Exception", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
If e.Cancelled Then
'worker was cancelled
Else
'worker finished. open Form2
f.Show
End If
End If
End Sub