Why are the codes for the animation show please wait... not working properly?

Question

Hi All

I extract data from Datagrid to Excel

According to these codes....

  Private Sub BtnExportToExcel_Click(sender As Object, e As EventArgs) Handles BtnExportToExcel.Click
      **  With FrmPleaseWait
            .MdiParent = MDI
            FrmPleaseWait.CircularProgress1.IsRunning = True
            FrmPleaseWait.CircularProgress1.Show()
            FrmPleaseWait.Show()
        End With**
        Dim Xlapp As Excel.Application
        Dim XlWorkBook As Excel.Workbook
        Dim XlWorkSheet As Excel.Worksheet
        Dim MisValue As Object = System.Reflection.Missing.Value
        Dim I As Integer
        Dim J As Integer
        Xlapp = New Excel.Application
        XlWorkBook = xlapp.Workbooks.Add(misValue)
        xlWorkSheet = CType(xlWorkBook.Sheets("Sheet1"), Excel.Worksheet)
        For K = 0 To DgvList.ColumnCount - 1
            XlWorkSheet.Cells(1, K + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
            XlWorkSheet.Cells(1, K + 1) = DgvList.Columns(K).HeaderText
        Next
        For i = 0 To DgvList.RowCount - 1
            For j = 0 To DgvList.ColumnCount - 1
                xlWorkSheet.Cells(i + 2, j + 1).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
                xlWorkSheet.Cells(i + 2, j + 1) =
                    DgvList(j, i).Value.ToString()
            Next
        Next
        Dim SaveFileDialog1 As New SaveFileDialog()
        SaveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx"
        SaveFileDialog1.FilterIndex = 2
        SaveFileDialog1.RestoreDirectory = True
        If SaveFileDialog1.ShowDialog() = DialogResult.OK Then
            xlWorkSheet.SaveAs(SaveFileDialog1.FileName)
         Else
            FrmPleaseWait.Close()
            Return
        End If
        XlWorkBook.Close()
        Xlapp.Quit()
        FrmPleaseWait.Close()
      End Sub

This process takes some time and I want to entertain the user with the CircularProgress control during the processing by the software. I created a form called FrmPleaseWait for this And in this line I show the code of the form to entertain the user

   With FrmPleaseWait
            .MdiParent = MDI
            FrmPleaseWait.CircularProgress1.IsRunning = True
            FrmPleaseWait.CircularProgress1.Show()
            FrmPleaseWait.Show()
        End With

My problem is that it is animated

during the processing of the code Is Stopped, and after sending the information to Excel and when saving the Excel file before this lines ...

  If SaveFileDialog1.ShowDialog() = DialogResult.OK Then

            xlWorkSheet.SaveAs(SaveFileDialog1.FileName)

         Else

            FrmPleaseWait.Close()

            Return

        End If

, the animation started.

In your opinion, how should I fix it so When I call the FrmPleaseWait, the animation should also work ?

Answer 1

Let's Excel processing into Task using async/await.

Imports System
Imports System.Windows.Forms
Imports System.Threading.Tasks
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1

    Private DgvList As DataGridView
    Private WithEvents BtnExportToExcel As Button
    Sub New()


        InitializeComponent()

        BtnExportToExcel = New Button()
        Me.Controls.Add(BtnExportToExcel)

        DgvList = New DataGridView() With {.Top = BtnExportToExcel.Height + 2, .Width = Me.ClientSize.Width}
        DgvList.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Column1"})
        DgvList.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Column2"})
        DgvList.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Column3"})

        Me.Controls.Add(DgvList)


        Dim i As Integer
        For i = 1 To 100
            Me.DgvList.Rows.Add()
            Dim r As DataGridViewRow = Me.DgvList.Rows(i - 1)

            r.Cells.Item(0).Value = i
            r.Cells.Item(1).Value = Math.Exp(i)
            r.Cells.Item(2).Value = DateTime.Now

        Next
    End Sub

    Private Async Sub BtnExportToExcel_Click(sender As Object, e As EventArgs) Handles BtnExportToExcel.Click

        Dim SaveFileDialog1 As New SaveFileDialog()
        SaveFileDialog1.Filter = "Execl files (*.xlsx)|*.xlsx"
        SaveFileDialog1.FilterIndex = 2
        SaveFileDialog1.RestoreDirectory = True
        If SaveFileDialog1.ShowDialog() <> DialogResult.OK Then
            Return
        End If

        Dim FrmPleaseWait As New FrmPleaseWait
        FrmPleaseWait.Show(Me)

        Me.Enabled = False
        Try
            Await Task.Run(Sub()
                               Export(SaveFileDialog1.FileName)
                           End Sub)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Me.Enabled = True

        FrmPleaseWait.Close()

    End Sub

    Sub Export(filePath As String)

        Dim valueArray(0 To DgvList.Rows.Count, 0 To DgvList.ColumnCount - 1) As Object

        For K = 0 To DgvList.ColumnCount - 1
            valueArray(0, K) = DgvList.Columns(K).HeaderText
        Next

        For r = 0 To DgvList.RowCount - 1
            Dim dgvr As DataGridViewRow = DgvList.Rows(r)
            For c = 0 To DgvList.ColumnCount - 1
                valueArray(r + 1, c) = dgvr.Cells(c).Value '?.ToString()
            Next
        Next


        Dim Xlapp As Excel.Application = Nothing
        Dim XlWorkBooks As Excel.Workbooks = Nothing
        Dim XlWorkBook As Excel.Workbook = Nothing
        Dim XlWorkSheets As Excel.Sheets = Nothing
        Dim XlWorkSheet As Excel.Worksheet = Nothing
        Dim cells As Excel.Range = Nothing
        Dim rngA As Excel.Range = Nothing
        Dim rngB As Excel.Range = Nothing
        Dim rng As Excel.Range = Nothing

        Dim MisValue As Object = System.Reflection.Missing.Value
        Try
            Xlapp = New Excel.Application
            Xlapp.Visible = True

            XlWorkBooks = Xlapp.Workbooks
            XlWorkBook = XlWorkBooks.Add(MisValue)

            XlWorkSheets = XlWorkBook.Worksheets
            XlWorkSheet = CType(XlWorkSheets.Item("Sheet1"), Excel.Worksheet)

            cells = XlWorkSheet.Cells
            rngA = cells.Item(1, 1)
            rngB = cells.Item(1 + DgvList.Rows.Count, DgvList.Columns.Count)

            rng = XlWorkSheet.Range(rngA, rngB)
            rng.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter
            rng.Value = valueArray

            Try
                XlWorkSheet.SaveAs(filePath)
            Finally
                XlWorkBook.Saved = True
                XlWorkBook.Close()
                Xlapp.Quit()
            End Try
        Finally
            If rng IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(rng) : End If
            If rngA IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(rngA) : End If
            If rngB IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(rngB) : End If
            If cells IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(cells) : End If
            If XlWorkSheet IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(XlWorkSheet) : End If
            If XlWorkSheets IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(XlWorkSheets) : End If
            If XlWorkBook IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(XlWorkBook) : End If
            If XlWorkBooks IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(XlWorkBooks) : End If
            If Xlapp IsNot Nothing Then : System.Runtime.InteropServices.Marshal.ReleaseComObject(Xlapp) : End If
        End Try

    End Sub
End Class

Class FrmPleaseWait
    Inherits System.Windows.Forms.Form

    Dim WithEvents timer As New Timer With {.Interval = 10}

    Private Sub FrmPleaseWait_Shown(sender As Object, e As EventArgs) Handles Me.Shown
        timer.Start()
    End Sub

    Private Sub timer_Tick(sender As Object, e As EventArgs) Handles timer.Tick
        Me.Text = DateTime.Now.ToString("HH:mm:ss.fff")
    End Sub
End Class