How to Save Excel without an user prompt to save the file?

Question

Hi I wrote some code using VBScript on Excel sheet as below. Now everytime when the Script is done its processing it is prompting the user to Save it.But I don't want this,rather I want it to save it automatically without prompt.

CODE

    Option Explicit

    Dim objExcel1,strPathExcel1,objSheet1,objSheet5

    Set objExcel1 = CreateObject("Excel.Application")'Object for Condition Dump
    strPathExcel1 = "D:\VA\GE_Wing_To_Wing_Report.xlsx"
    objExcel1.Workbooks.Open strPathExcel1
    Set objSheet1 = objExcel1.ActiveWorkbook.Worksheets(1)
    Set objSheet5 = objExcel1.ActiveWorkbook.Worksheets(5)

    '=====================================================================================
    'Here Bad sheet will be copied by the data from First sheet master data sheet
    '=====================================================================================
       ParentPIDFromMasterSheet objSheet1,objSheet5

    '=====================================================================================
    'Here Bad sheet will be copied by the data from First sheet master data sheet
    '=====================================================================================
       BadDataSelectionDel objSheet5

    '=======================
    objExcel1.ActiveWorkbook.SaveAs strPathExcel1
    objExcel1.Workbooks.close
    objExcel1.Application.Quit
    '======================

Related: Open an Excel file and save as .XLS

Stevoisiak
– Stevoisiak

2018-09-05 14:40:43 +00:00
Commented Sep 5, 2018 at 14:40 — Stevoisiak
– Stevoisiak, Commented Sep 5, 2018 at 14:40

Siddharth Rout · Accepted Answer · 2012-12-26 07:14:01Z

4

UNTESTED (Try this)

You need to set your workbook and then close it after saving it. Also it is a good practice to clean up your objects at the end of your code after use. :)

Option Explicit

Dim objExcel1, objWB, strPathExcel1, objSheet1, objSheet5

Set objExcel1 = CreateObject("Excel.Application") 'Object for Condition Dump
strPathExcel1 = "D:\VA\GE_Wing_To_Wing_Report.xlsx"
Set objWB = objExcel1.Workbooks.Open(strPathExcel1)
Set objSheet1 = objWB.Worksheets(1)
Set objSheet5 = objWB.Worksheets(5)

'=====================================================================================
'Here Bad sheet will be copied by the data from First sheet master data sheet
'=====================================================================================
   ParentPIDFromMasterSheet objSheet1, objSheet5

'=====================================================================================
'Here Bad sheet will be copied by the data from First sheet master data sheet
'=====================================================================================
   BadDataSelectionDel objSheet5

'=======================
objWB.Save
objWB.Close
objExcel1.Quit

'~~> Cleanup
Set objSheet1 = Nothing
Set objSheet5 = Nothing
Set objWB = Nothing
Set objExcel1 = Nothing
'======================

answered Dec 26, 2012 at 7:14

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dexter Whelan Over a year ago

hi Siddharth, why is it good practice to close yoru objects? what are the implications? :) #generalcuriosity

Siddharth Rout Over a year ago

Very Good Question :) If you do a google search you will get your answer. This has been widely covered

Stevoisiak Over a year ago

To save with a specific filename, you can use objWB.SaveAs "C:\path\to\file\filename.xlsx" (See Open an Excel file and save as .XLS)

Hamza · Accepted Answer · 2020-03-22 13:03:56Z

0

You only need to add this line of code before saving your file :

objExcel1.Application.DisplayAlerts = False 'Prevents prompts from appearing

I tried it and it worked.

answered Mar 22, 2020 at 13:03

Hamza

1

Collectives™ on Stack Overflow

How to Save Excel without an user prompt to save the file?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related