7 Replies - 910 Views - Last Post: 07 May 2015 - 12:09 PM Rate Topic: -----

#1 Brandalf   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-May 15

VB.NET saving excel file issue

Posted 06 May 2015 - 03:24 PM

Hi, This is my first post so hopefully I have explained myself well enough. Basically Ive got a datagridview that takes information from multiple textboxes on a form and should loop round and save them into an excel file. However when I save the excel file I get the issue of if the user clicks "No" or "Cancel" instead of over writing the file like I had intended them to do as it is extracted daily to another excel file. It basically causes the system to jump out. I don't know much about the saving of files in VB as its my first time tackling the problem I just need to get the code to not jump out if the user clicks no or cancel when given the option to overwrite the file. Really hope you guys have an idea everything Ive tried hasn't worked. The code will be posted below.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Dailyreport
    Private Sub Submitbtn_Click(sender As Object, e As EventArgs) Handles Submitbtn.Click

        Dim regDate As Date = Date.Now()
        Dim strDate As String = regDate.ToString("ddMMMyyyy")

        ''This will allown the hidden form Exceldump to make its datagrid global then populate it with values from the textboxes.
        Exceldump.DataGridView1.Rows.Add(Advisor1txtbox.Text, Advisor2txtbox.Text, Extraadvisortxtbox.Text, Advisor1combo.Text, Advisor2combo.Text, Extraadvisorcombo.Text, Callstxtbox.Text, Escalatedcalltxtbox.Text, PATcalltxtbox.Text, Opentxtbox.Text, Roomcheckcombo.Text, Surveytxtbox.Text, strDate)

        ''This code will simply display a message box so users will understand which option to take.
        ''The box is placed before the actual save so that the user will know before the program has a chance to display its option.
        Dim result As Integer = MessageBox.Show("Is the Helpdesk Daily Report entered correctly?", "H-CSS confirmation", MessageBoxButtons.YesNo)
        If result = DialogResult.No Then
            MessageBox.Show("Please re-enter the Daily Report Data.", "H-CSS Alert")
        ElseIf result = DialogResult.Yes Then
            MessageBox.Show("When prompted to overwrite existing excel file please select the yes option.", "H-CSS Alert")

            ''This code simply sets up the extensions and variables to handle the generation of an excel workbook.
            ''Although the code attempts to create a new excel workbook each time the idea is to overwrite the existing one.
            ''This is because the final format daily report excel sheet will take the values from this "dump" file.

            Dim xlApp As Microsoft.Office.Interop.Excel.Application
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer

            ''This code sets up the Workbook and sheetname that the data will be saved to.

            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")

            ''This code will loop around the datagridview and will extract the data aswell as headers.
            ''This is because the headers will be used as a reference in the real time data transfer.
            For i = 0 To Exceldump.DataGridView1.RowCount - 2
                For j = 0 To Exceldump.DataGridView1.ColumnCount - 1
                    For k As Integer = 1 To Exceldump.DataGridView1.Columns.Count
                        xlWorkSheet.Cells(1, k) = Exceldump.DataGridView1.Columns(k - 1).HeaderText
                        xlWorkSheet.Cells(i + 2, j + 1) = Exceldump.DataGridView1(j, i).Value.ToString()
                    Next
                Next
            Next

            ''This code will simply display a message box so users will understand which option to take.
            ''The box is placed before the actual save so that the user will know before the program has a chance to display its option.


            ''This code simply saves the file at a given path closes the book then the software. 
            '' xlWorkSheet.SaveAs("C:\Helpzone\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()

            ''This code uses the subroutine release object as a fail safe to manage the unmanaged memory so that the program does not crash
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)

            MsgBox("Daily report has been saved successfully.")
        End If
    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


    Private Sub Quitbtn_Click(sender As Object, e As EventArgs) Handles Quitbtn.Click
        Close()
    End Sub
End Class


Is This A Good Question/Topic? 0
  • +

Replies To: VB.NET saving excel file issue

#2 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1538
  • View blog
  • Posts: 3,869
  • Joined: 01-February 13

Re: VB.NET saving excel file issue

Posted 06 May 2015 - 04:42 PM

I am not sure if this would help but, you can force the excel app to skip the dialog all together and just overwrite the file. That way the user is not prompted with the dialog and would not be able to choose not to save/overwrite the file.

Just put this line right before you save the file.
xlApp.DisplayAlerts = False

Was This Post Helpful? 1
  • +
  • -

#3 Brandalf   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-May 15

Re: VB.NET saving excel file issue

Posted 06 May 2015 - 04:47 PM

Thanks Ironrazer that may be what iam looking for if it prevents the user from having a choice it should solve the issue. Also I did notice when you select no at the prompt and shut down the computer it asked whether to save book1 which I assume is the default file creation if the file is not overwritten? Would this maybe solve that as well?
Was This Post Helpful? 0
  • +
  • -

#4 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1538
  • View blog
  • Posts: 3,869
  • Joined: 01-February 13

Re: VB.NET saving excel file issue

Posted 06 May 2015 - 05:33 PM

Quote

Would this maybe solve that as well?

I can`t say for sure, you will have to try it out and see.

If it still asks after you have saved the file and you shut down the computer then it may be Excel is not closing properly. However, it appears you are closing and releasing all references to it. 8)
Was This Post Helpful? 0
  • +
  • -

#5 _HAWK_   User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: VB.NET saving excel file issue

Posted 06 May 2015 - 08:04 PM

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)


Is the method you should be using. It calls ReleaseComObject in a loop til the object is disposed of.

This post has been edited by _HAWK_: 06 May 2015 - 09:19 PM

Was This Post Helpful? 1
  • +
  • -

#6 Brandalf   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-May 15

Re: VB.NET saving excel file issue

Posted 07 May 2015 - 01:22 AM

View Post_HAWK_, on 06 May 2015 - 08:04 PM, said:

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)


Is the method you should be using. It calls ReleaseComObject in a loop til the object is disposed of.


Thanks IronRazer I will try your code when I get back from work.

Thanks _Hawk_ any hints and tips are appreciated I've never done file handling before this project.
What should I change out for the
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)

I basically modified the object release code so iam not 100% on what it does exactly.
Was This Post Helpful? 0
  • +
  • -

#7 _HAWK_   User is offline

  • Master(Of Foo)
  • member icon

Reputation: 1162
  • View blog
  • Posts: 4,444
  • Joined: 02-July 08

Re: VB.NET saving excel file issue

Posted 07 May 2015 - 05:55 AM

It should replace your ReleaseComObject line in your releaseObject method.
Was This Post Helpful? 0
  • +
  • -

#8 Brandalf   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 06-May 15

Re: VB.NET saving excel file issue

Posted 07 May 2015 - 12:09 PM

View Post_HAWK_, on 07 May 2015 - 05:55 AM, said:

It should replace your ReleaseComObject line in your releaseObject method.


Thanks very much both strands of code work and now my program is alot more robust to users.
I appreciate the help

Regards,

Brandalf
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1