I've got a VB.NET 2005 application that exports data to an Excel worksheet. The problem I'm having is that if the file the user wants to save to already exists I get two confirmations to overwrite prompts. One of them occurs in the file dialog box after they select their file name. The other occurs when I do a SaveAs to save the file. Also, if I click on "No" on the second prompt a System.Runtime.InteropServices.COMException error occurs.

I start off the subroutine thusly:

Dim file As New SaveFileDialog
  With file
    'Set the default extension to xls
    .DefaultExt = "xls"
    'Prompt them if the file exists and ask if its okay to overwrite
    .OverwritePrompt = True
    'Set the initial directory to the user's My Documents folder
    .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocume nts
    'In the file list, display only xls files, but allow them to change to view all files
    .Filter = "Excel Workbook files (*.xls)|*.xls|All files (*.*)|*.*"
    'Set the filter index to 1, indicating the xls files
    .FilterIndex = 1
  End With
Dim filePath As String = ""

'If the user clicked "Save" (OK), then continue with the export
If file.ShowDialog = System.Windows.Forms.DialogResult.OK Then
  Try
     My.Application.DoEvents() 'makes the dialog box go away
     filePath = file.FileName 'gets the filename

     'Create the Excel worksheet
     Dim app As Application
     app = New Application()
     Dim workbooks As Workbooks
     workbooks = app.Workbooks
     Dim workbook As _Workbook
     workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet)
     Dim sheets As Sheets
     sheets = workbook.Worksheets
     Dim worksheet As _Worksheet
     worksheet = sheets.Item(1)


Next I retrieve the data from the mainframe and populate the Excel cells. At the end of this process I save the file with:

workbook.SaveAs(filePath)
workbook.Close()

Is there a way I can disable the prompting on the workbook.SaveAs(filepath) line?

Thanks for any help.

John

Just turn of the Alerts. It should solve the problem

Application.DisplayAlerts = False

workbook.SaveAs(filePath)

Application.DisplayAlerts = True

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

I tried Application.DisplayAlerts = False but got a syntax error of "Reference to a non-shared member requires an object reference."

I discovered that Application.DisplayAlerts is part of the Excel PIA and that if I used my application variable app it would work:

app.DisplayAlerts = False

Thanks, Shasur!