CODEWITHSUNDEEP
vb.netexcel
rakete
rakete

Reputation: 3041

How can I overwrite an other Excel-file without the Dialog "Really want to overwrite" in VB.NET

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?

Upvotes: 3

Views: 26262

Answers (7)

GERMAN RODRIGUEZ
GERMAN RODRIGUEZ

Reputation: 515

Dialog box avoided with (Application.DisplayAlerts = False) before saving command and (Application.DisplayAlerts = True) after:

Workbooks.Open "C:\XML\baseline\RTWP\Analisis.xlsb"
Windows("Analisis.xlsb").Activate
Sheets("Data").Select
Cells.Select
Selection.ClearContents
Workbooks.Open "C:\XML\baseline\RTWP\output\totemplate.csv"
Windows("totemplate.csv").Activate
Sheets("totemplate").Select
Cells.Select
Selection.Copy
Windows("Analisis.xlsb").Activate
Range("A1").Select
ActiveSheet.Paste
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs fileName:="C:\XML\baseline\RTWP\Analisis.xlsb", FileFormat:=xlExcel12, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close SaveChanges:=False
Windows("totemplate.csv").Activate
ActiveWorkbook.Close SaveChanges:=False

Upvotes: 0

rdavidson
rdavidson

Reputation: 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

Upvotes: 0

Nono
Nono

Reputation: 1

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

Upvotes: 0

sansalk
sansalk

Reputation: 4741

 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)

Upvotes: 2

t0mm13b
t0mm13b

Reputation: 34592

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

Hope this helps.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166416

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);

Upvotes: 7

Prof. Falken
Prof. Falken

Reputation: 24887

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

Upvotes: 1

Related Questions