How can I overwrite an other Excel-file without th

Question

How can I save an Excel-file, which I'd edit with VB.NE, to a file, which already exists? Evertime there is a dialog: File already exists. Do you really want to overwrite? YES|NO|Abort

How can I overwrite without this dialog?

Answer 1

I resolve this issue by using the Object.Save() method instead. I tried to use the SaveAs method even with the DisplayAlerts = $false but it still kept giving me the overwrite warning.

Code Snippet Below:

# Create an Object Excel.Application using Com interface
$objExcel = New-Object -ComObject Excel.Application
# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $true
# Override warning messages about existing file
$objExcel.DisplayAlerts = $false
# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)
# Load the WorkSheet 'BuildSpecs'
$WorkSheet = $WorkBook.sheets.item($SheetName)
# Pause execution to allow data refresh to occur (about 5 minutes to be safe)
Start-Sleep -s 300
# Now the Save the file After Refreshing (need to add a pause for about 5 minutes)
$WorkBook.Save()
# Wait while saving before closing excel object
Start-Sleep -s 30
# Now close the workbook

Answer 2

There is a property within the SaveFileDialog class, called OverwritePrompt, set that to false.

Hope this helps.

Answer 3

 Dim xlWorkBook As Excel.Workbook
 Dim xlWorkSheet As Excel.Worksheet
 Dim misValue As Object = System.Reflection.Missing.Value

 xlWorkBook.SaveAs(<YourFileName>, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue,   misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
 xlWorkBook.Close(True, misValue, misValue)

Answer 4

Couldn't you try to delete the file first, before overwriting it?

Answer 5

You should have a look at setting

DisplayAlerts=false

Application.DisplayAlerts Property

Set this property to False if you don’t want to be disturbed by prompts and alert messages while a program is running; any time a message requires a response, Microsoft Excel chooses the default response.

Just remember to reset it to true once you are done.

We currently do it as follows

object m_objOpt = Missing.Value;
m_Workbook.Application.DisplayAlerts = false;
m_Workbook.SaveAs(  fileName, m_objOpt, m_objOpt, 
                    m_objOpt, m_objOpt, m_objOpt, 
                    XlSaveAsAccessMode.xlNoChange, 
                    XlSaveConflictResolution.xlLocalSessionChanges, 
                    m_objOpt, m_objOpt, m_objOpt, m_objOpt);

Answer 6

I solved the problem setting the third parameter of xlApp.Workbooks.Open to false when you create the workbook. That is the readonly argument and if it is set to true it will ask for saving file.

Sub ExcelMacroExec2()
    Dim xlApp, xlBook

    Set xlApp = CreateObject("Excel.Application")
    xlApp.DisplayAlerts = False

    Set xlBook = xlApp.Workbooks.Open("C:\Users\A\myFile.xlsm", 0, False)
    xlApp.Run "Macro1"   

    xlApp.Save 
    xlBook.Close false
    xlApp.Quit   

    Set xlApp = Nothing
    set xlBook = Nothing
End Sub