Save excel workbook into the current workbook automatically with no prompt

HettyA

New Member
Joined
Aug 20, 2020
Messages
5
Office Version
  1. 2007
Platform
  1. Windows
Hello,

How do i save without the prompt to save as every time i run my Vb script. I'd like it to automatically save into my existing workbook rather than open a new path or prompt me to save all the time.

Thanks

'Input Excel File's Full Path
ExcelFilePath = "C:\Users\Student\Desktop\Tester.xlsm"

'Input Module/Macro name within the Excel File
MacroPath = "Sheet1.refresh1"

'Create an instance of Excel
Set ExcelApp = CreateObject("Excel.Application")

'Do you want this Excel instance to be visible?
ExcelApp.Visible = True ' or False

'Prevent any App Launch Alerts (ie Update External Links)
ExcelApp.DisplayAlerts = False

'Open Excel File
Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
ExcelApp.Run MacroPath

'Reset Display Alerts Before Closing
ExcelApp.DisplayAlerts = True

'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
Set wbToRun = ExcelApp.Workbooks.Open(ExcelFilePath)
ExcelApp.Run MacroPath ' wbToRun.Name & "!" & MacroPath
wbToRun.Save
wbToRun.Close
ExcelApp.Quit
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,763
Office Version
  1. 2013
Platform
  1. Windows
It looks like you have duplicated the process of opening the workbook and running the macro. Maybe if you comment out the second set of commands to open and run it won't display the alert.

Rich (BB code):
'Open Excel File
 Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)

'Execute Macro Code
 ExcelApp.Run MacroPath

'Reset Display Alerts Before Closing
  ExcelApp.DisplayAlerts = True

'Open workbook; Run Macro; Save Workbook with changes; Close; Quit Excel
 Set wbToRun = ExcelApp.Workbooks.Open(ExcelFilePath) 
ExcelApp.Run MacroPath     '   wbToRun.Name & "!" & MacroPath

also you can close and save in one statement.


VBA Code:
 wbToRun.Close  True
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,892
Members
412,689
Latest member
nhsmedic
Top