Excel's MessageBoxes

[Trancedified]

Hello,

I am querying SQL Server 2000 and then writing the results into Excel below. If the file like C:\myexcel.xls already exists in the directory a messagebox that I didnt program pops up asking if I would like to replace it ... Yes, No, or Cancel.
When I click "No" or "Cancel" I get an error:

"SaveAs method of Workbook class failed"

Here is my code:

           Opens "Save As" dialog box
            Dim objSaveDialog As New System.Windows.Forms.SaveFileDialog

            If the user omits Extension, add for them. Specify
            the default extension to use as well.
            objSaveDialog.AddExtension = True
            objSaveDialog.DefaultExt = ".xls"

            Prompt the user to create a file if it doesnt exist or overwrite if it does exist.
            objSaveDialog.CreatePrompt = False
            objSaveDialog.OverwritePrompt = False

            Show the help button
            objSaveDialog.ShowHelp = False

            Set the Initial Directory
            objSaveDialog.InitialDirectory = "C:\"

            Set the Filter of File types to be opened
            objSaveDialog.Filter = "xls files (*.xls)|*.xls|" & "PDF files (*.pdf)|*.pdf|" & _
                "All files (*.*)|*.*"

            Set the Default Filter Index to the first item in the .Filter
            objSaveDialog.FilterIndex = 1

            Verify the file and path exist
            objSaveDialog.CheckPathExists = True

            Return the path of the file a shortcut references if it is a shortcut .lnk file the dialog found. 
            Set to False to return the actual location of the 
            .lnk file instead.
            objSaveDialog.DereferenceLinks = True

            Initial filename to be used
            objSaveDialog.FileName = "test.xls"

            Title of the Dialog
            objSaveDialog.Title = "Save As"

            Allow only Valid File Names (no ",|,<,> characters)
            objSaveDialog.ValidateNames = True

            If you want the dialog to open using the last directory you were in, then set this to 
            False and comment the .InitialDirectory.
            objSaveDialog.RestoreDirectory = False


               Open the file as a stream to read or write
                Dim objExcel As Object
                Dim objBook As Object
                Dim objSheet As Object
                Dim strSQL As String
                objExcel = CreateObject("Excel.Application")
                objBook = objExcel.Workbooks.Add
                objSheet = objBook.Worksheets(1)

                strSQL = "SELECT * FROM MyTable"

                Set up the Query Table and tell it where to find the data. 
                Dim objQryTable As Object
                objQryTable = objSheet.QueryTables.Add("OLEDB;Provider=sqloledb;Data Source=MyServerName" & _
                    ";Initial Catalog=My Database" & _
                    ";Trusted Connection=Yes;Integrated Security=SSPI;", objSheet.Range("A1"), strSQL)
                objQryTable.RefreshStyle = 2  x1InsertEntire Rows = 2 
                objQryTable.Refresh(False)

                objBook.SaveAs("C:\myexcel.xls")
                Clear everything so you can display it to the user 
                objQryTable = Nothing
                objSheet = Nothing
                objBook = Nothing
                objExcel.Quit()
                objExcel = Nothing

Any ideas?

Chris

 

[HJB417]

Not sure if this will do it, but after the line
objBook.SaveAs("C:\myexcel.xls")
If you add
objBook.Saved = True
WIll it still prompt you?

 

[Arch4ngel]

The remove the message box... theres a property that tell not to pop-up for messages.

oExcel.DisplayAlerts = false

But if the message appear... its not for nothing. Your workbook might not be saved. You can save it while closing :

object oMiss = System.Reflection.Missing.Value
m_wb.Close(true, filename, oMiss)

It shall save everything that have been modified and close the workbook.

 

[Trancedified]

oExcel.DisplayAlerts = false


did the trick and I flipped on:

objSaveDialog.OverwritePrompt = True

So objSaveDialog will handle the Yes or No to overwrite an existing file.

Thanks for your help!

Chris