Data doesn't save to an Excel file unless a MsgBox appears first

Question

Can someone explain why my code won't let me save data to Excel unless I include a MsgBox?

Here is my code:

Sub createreport()
        Try
            Dim XA As New Excel.Application
            Dim wb As Excel.Workbook
            Dim ws As Excel.Worksheet
            wb = XA.Workbooks.Open(dataDirectory + "employee_info\dtr_emp.xlsx", False, False, True)
            ws = wb.Worksheets("Sheet1")

            MsgBox("Test") '<---- THIS IS THE MSGBOX I WAS TALKING ABOUT

            For i As Integer = 0 To Me.EmployeeInfoDataGridView.Rows.Count - 1
                Dim DGV As DataGridViewRow = Me.EmployeeInfoDataGridView.Rows(i)
                ws.Cells(7 + i, 1) = DGV.Cells(0).Value
                ws.Cells(7 + i, 2) = DGV.Cells(1).Value
                ws.Cells(7 + i, 3) = DGV.Cells(2).Value
                ws.Cells(7 + i, 4) = DGV.Cells(3).Value
                ws.Cells(7 + i, 5) = DGV.Cells(4).Value
                ws.Cells(7 + i, 6) = DGV.Cells(5).Value
                ws.Cells(7 + i, 7) = DGV.Cells(6).Value
                ws.Cells(7 + i, 8) = DGV.Cells(7).Value
                ws.Cells(7 + i, 9) = DGV.Cells(8).Value
                ws.Cells(7 + i, 10) = DGV.Cells(9).Value
                ws.Cells(7 + i, 11) = DGV.Cells(10).Value
                ws.Cells(7 + i, 12) = DGV.Cells(12).Value
                ws.Cells(7 + i, 13) = DGV.Cells(14).Value
            Next
            XA.Visible = False

            wb.SaveAs(dataDirectory + "employee_info\temp_" + Form1.lbl_date.Text + ".xlsx")
            wb.Close(True)
            XA.Quit()
            wb = Nothing : ws = Nothing : XA = Nothing
            Try
                My.Computer.FileSystem.CopyFile("employee_info\temp_" + Form1.lbl_date.Text + ".xlsx", "employee_info\employee_infos.xlsx", True)
                My.Computer.FileSystem.DeleteFile("employee_info\temp_" + Form1.lbl_date.Text + ".xlsx", FileIO.UIOption.OnlyErrorDialogs, FileIO.RecycleOption.DeletePermanently, FileIO.UICancelOption.DoNothing)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        exit_excel_process.Show()
    End Sub

No data is saved to Excel file unless I put that MsgBox code in.

It seems the code runs faster than your computer can open the file. Have a look at this: http://stackoverflow.com/questions/33817414/wait-for-big-files-to-open-in-excel — Andreas, Nov 21 '16 at 14:21
Thanks for the response, I tried writing Threading.Thread.Sleep(10000) this is 10 seconds but it seems the problem is still there — Miguel Gaurano Punzal, Nov 21 '16 at 14:33
I tried the codes that you have given, but still it doesnt work. Still that MsgBox the answer, but it is so annoying to see a msgbox in a window call — Miguel Gaurano Punzal, Nov 21 '16 at 14:38
When you say you use a `MsgBox` do you wait a period of time before clicking OK or do you click it right away? Also out of curiosity what happens if you load up a blank workbook (this is just to test the speed as a blank workbook would load quicker than a workbook full of rows). — Bugs, Nov 21 '16 at 14:59
After that MsgBox shows up i immediately click the OK button then it saves to Excel. It also works even though I wait a few seconds before pressing OK. is there a way I can display MsgBox and programatically press OK right away? — Miguel Gaurano Punzal, Nov 21 '16 at 15:08
I wouldn't look at trying to do that. Try replacing with `Application.DoEvents` and if that doesn't work have a look at [this](http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba). Slightly different scenario but could prove useful. — Bugs, Nov 21 '16 at 15:41
Tried it, still doesn't work. I hope I will resolve this as soon as possible. BTW thanks for the replies. — Miguel Gaurano Punzal, Nov 21 '16 at 17:12

score 0 · Answer 1 · answered Dec 08 '16 at 20:26

Since the Try is not working, the focus is getting kicked out to Catch. This seems overly complicated to do it this way. Please see the code sample below. Can you work around that?

Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click

        Dim cnn As SqlConnection
        Dim connectionString As String
        Dim sql As String

        connectionString = "data source=servername;" & _
        "initial catalog=databasename;user id=username;password=password;"
        cnn = New SqlConnection(connectionString)
        cnn.Open()
        sql = "SELECT * FROM Product"
        Dim dscmd As New SqlDataAdapter(sql, cnn)
        Dim ds As New DataSet
        dscmd.Fill(ds)
        DataGridView1.DataSource = ds.Tables(0)
        cnn.Close()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, _
    ByVal e As System.EventArgs) Handles Button2.Click


        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.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For i = 0 To DataGridView1.RowCount - 2
            For j = 0 To DataGridView1.ColumnCount - 1
                xlWorkSheet.Cells(i + 1, j + 1) = _
                    DataGridView1(j, i).Value.ToString()
            Next
        Next

        xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

        MsgBox("You can find the file C:\vbexcel.xlsx")
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Data doesn't save to an Excel file unless a MsgBox appears first

1 Answers1