DOS command to close Excel with saving the changes

[bromberg]

Is there a DOS command that would save the changes of my EXCEL workbook and then close the file?

Thanks in advance to all who reply,
Dan

 

[PatOBrien198]

No.

However, you could create a VBS (windows script) file that could do it, then the cmd prompt or DOS command could run the vbs file...

Dim objExcel, objWB
dim i
dim WB_To_Close
set objExcel = GetObject(, "Excel.Application")
WB_To_Close = "Test_To_Close.xlsx"  ' enter name of WB to be closed here
for i = 1 to objExcel.Workbooks.Count
    set objWB = objExcel.Workbooks(i)
    if objWB.Name = WB_To_Close then
        objWB.Save
        objWB.Close
        exit for
    end if
    i = i + 1
next
'if there are no more workbooks open in that instance of excel, then quit excel
if objExcel.Workbooks.Count = 0 then 
    objExcel.Quit
end if

 

[bromberg]

No.

However, you could create a VBS (windows script) file that could do it, then the cmd prompt or DOS command could run the vbs file...

Dim objExcel, objWB
dim i
dim WB_To_Close
set objExcel = GetObject(, "Excel.Application")
WB_To_Close = "Test_To_Close.xlsx"  ' enter name of WB to be closed here
for i = 1 to objExcel.Workbooks.Count
    set objWB = objExcel.Workbooks(i)
    if objWB.Name = WB_To_Close then
        objWB.Save
        objWB.Close
        exit for
    end if
    i = i + 1
next
'if there are no more workbooks open in that instance of excel, then quit excel
if objExcel.Workbooks.Count = 0 then 
    objExcel.Quit
end if

Pat:
Since I'm not familiar with VBS, thanks for your code, but just a few more questions:
1) Do I create this file using NOTEPAD and rename it with a VBS extension?
2) Do I have to give the full path-name in your WB_To_Close field?
3) Once created, what directory do I save it to?

Or if you can recommend a site that answers my questions (or has an example) just provide the link.

Thanks again!
Dan

 

[PatOBrien198]

Here's a better version.... turns out that I have a need for this as well. I need to make sure that no Excel workbooks are open before my automated Excel WB processor kicks off at 2:00 a.m. every morning. It is started with a Windows Task Scheduler job and breaks if I forget to save and close out of Excel.

1) Yes, use notepad and save this as a file with the .vbs extension... I named mine CloseExcelWB.vbs
2) The script looks at just the Name of the Workbook, not the full path. It could be changed to look at the full path
if UCase(objWB.Name) = UCase(WB_To_Close) then
could be changed to:
if UCase(objWB.Path) = UCase(WB_To_Close) then

3) This could be saved to any folder / directory. If you want it to be able to be called from anywhere, save it in a folder already in your "PATH"

I'm planning on saving it to the folder where my other automated workbook resides and set up a Task to call a bat file that has just one line...
CloseExcelWB All

You could execute it either one WB at a time or create a bat file that calls it multiple times for individual WBs....
CloseExcelWB myWB1
CloseExcelWB myWB2

Dim objExcel
Dim objWB
dim i
dim WB_To_Close
dim msg

if wscript.arguments.Count = 0 then 
    Call Syntax
    wscript.quit
end if

WB_To_Close = wscript.arguments.item(0)
set objExcel = GetObject(, "Excel.Application")

If UCase(WB_To_Close) = "ALL" then
    Do While ObjExcel.Workbooks.Count > 0
        set objWB = objExcel.Workbooks(1)
        objWB.Save
        objWB.Close
    Loop
Else
    for i = 1 to objExcel.Workbooks.Count
        set objWB = objExcel.Workbooks(i)
        if UCase(objWB.Name) = UCase(WB_To_Close) then
            objWB.Save
            objWB.Close
            exit for
        end if
    next
End If

if objExcel.Workbooks.Count = 0 then 
    objExcel.Quit
end if

wscript.quit

Sub Syntax()
    msg = "Syntax: " 
    msg = msg & vbCrLF & "CloseExcelWB [All | WB Name to Close.xls?]"
    msg = msg & vbCrLF & "    the All argument will save and close all WBs"
    msg = msg & vbCrLF & "    WB Name is not case sensitive"
    msg = msg & vbCrLF & "    Each WB to be closed is saved first"
    msg = msg & vbCrLF & "    If no more WBs are in the instance of Excel, then"
    msg = msg & vbCrLF & "        the instance of Excel is closed, otherwise"
    msg = msg & vbCrLF & "        the Excel application is not closed."
    msgbox msg
End Sub

 

[bromberg]

Here's a better version.... turns out that I have a need for this as well. I need to make sure that no Excel workbooks are open before my automated Excel WB processor kicks off at 2:00 a.m. every morning. It is started with a Windows Task Scheduler job and breaks if I forget to save and close out of Excel.

1) Yes, use notepad and save this as a file with the .vbs extension... I named mine CloseExcelWB.vbs
2) The script looks at just the Name of the Workbook, not the full path. It could be changed to look at the full path
if UCase(objWB.Name) = UCase(WB_To_Close) then
could be changed to:
if UCase(objWB.Path) = UCase(WB_To_Close) then

3) This could be saved to any folder / directory. If you want it to be able to be called from anywhere, save it in a folder already in your "PATH"

I'm planning on saving it to the folder where my other automated workbook resides and set up a Task to call a bat file that has just one line...
CloseExcelWB All

You could execute it either one WB at a time or create a bat file that calls it multiple times for individual WBs....
CloseExcelWB myWB1
CloseExcelWB myWB2

Dim objExcel
Dim objWB
dim i
dim WB_To_Close
dim msg

if wscript.arguments.Count = 0 then 
    Call Syntax
    wscript.quit
end if

WB_To_Close = wscript.arguments.item(0)
set objExcel = GetObject(, "Excel.Application")

If UCase(WB_To_Close) = "ALL" then
    Do While ObjExcel.Workbooks.Count > 0
        set objWB = objExcel.Workbooks(1)
        objWB.Save
        objWB.Close
    Loop
Else
    for i = 1 to objExcel.Workbooks.Count
        set objWB = objExcel.Workbooks(i)
        if UCase(objWB.Name) = UCase(WB_To_Close) then
            objWB.Save
            objWB.Close
            exit for
        end if
    next
End If

if objExcel.Workbooks.Count = 0 then 
    objExcel.Quit
end if

wscript.quit

Sub Syntax()
    msg = "Syntax: " 
    msg = msg & vbCrLF & "CloseExcelWB [All | WB Name to Close.xls?]"
    msg = msg & vbCrLF & "    the All argument will save and close all WBs"
    msg = msg & vbCrLF & "    WB Name is not case sensitive"
    msg = msg & vbCrLF & "    Each WB to be closed is saved first"
    msg = msg & vbCrLF & "    If no more WBs are in the instance of Excel, then"
    msg = msg & vbCrLF & "        the instance of Excel is closed, otherwise"
    msg = msg & vbCrLF & "        the Excel application is not closed."
    msgbox msg
End Sub

Pat:
Thanks for the macro - looking forward to getting it to save my Workbook when I shutdown with forgetting to do so.
Dan