My code closes the workbook but not the window

Question

I have a problem with closing window. I am using excel 2013 and it opens every workbook in separate window. My code closes the workbook but not the window. Any ideas? I am calling this sub from another sub.

Sub export_sheet()
    Sheets(sName).Move
    ActiveWorkbook.SaveAs Filename:=sDir & sName & ".xlsx",  FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks(sName & ".xlsx").Close SaveChanges:=True
    Workbooks(ThisWorkbook).Activate
End Sub

I have two Excel instances. One contains my code "temp 121015.xlsm" and the other one is just a empty window with the workbook name but the workbook is closed.

I dont want to close whole Excel application because i am calling this from a userform . i want the userform workbook to remain open. — Shan, Oct 11 '15 at 15:40
I mean in the processes tab, not the application tab, of the task manager — A.S.H, Oct 11 '15 at 15:48

score 3 · Answer 1 · answered Oct 11 '15 at 18:11

Excel 2013's method of an independent application window for each workbook means that you have to work with the Application.ActiveWindow property to close the new workboook that you created without leaving an empty application frame.

Sub export_sheet()
    Sheets(sName).Move
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=sDir & sName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close   '<~~ this closes the new workbook created by the .Move
    ThisWorkbook.Activate
    Application.DisplayAlerts = True
End Sub

You are already dealing with the ActiveWorkbook property created with the Worksheet.Move method. Continue working within this scope with ActiveWindow.

I'm not sure what the problem is. I've tested this on xl2013 and it worked as intended. Can you expand on 'it did not work'? I'm assuming that you are not receiving any errors, simply that you are left with an empty Excel application frame. (btw, the `& .xlsx` is unnecessary. If the .SaveAs is set for `FileFormat:=xlOpenXMLWorkbook` then the .xlsx will be added to the workbook's name.) — , Oct 11 '15 at 18:56

score 0 · Answer 2 · answered Oct 11 '15 at 14:47

0

Application.Quit

This will close the entire excel application instance. You will need to save changes before you use this line in your code, to prevent the save prompt.

answered Oct 11 '15 at 14:47

luke_t

2,935
4
22
38

A.S.H · Answer 3 · 2015-10-11T17:02:14.167

0

Please Try this:

Sub export_sheet()
    Sheets(sName).Move
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=sDir & sName & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks(sName & ".xlsx").Application.Quit
    ThisWorkbook.Activate
    Application.DisplayAlerts = True
End Sub

edited Oct 11 '15 at 17:02

answered Oct 11 '15 at 15:58

A.S.H

29,101
5
23
50

thanks but it did not work. still i have that empty window with the workbook name open. – Shan Oct 11 '15 at 16:49
@Shan as I asked you in the main post, do you see many Excel processes in the Processes tab of the Task manager? – A.S.H Oct 11 '15 at 16:58
hi i have attached the screen shot ..as you can see under excel there are two instances. – Shan Oct 11 '15 at 17:00
ok, then it means we have to close the instance that manages the new workbook. I edited my answer to do that. – A.S.H Oct 11 '15 at 17:01
@Shan the edited code tries to close the instance of excel attached to the new workbook. Please let me know it it works. – A.S.H Oct 11 '15 at 17:04
sorry I could not verify it here because my installation does not create many application instances, but I remember this had worked in the past. – A.S.H Oct 11 '15 at 17:12
sorry ..it tries to close all the instances of excel. – Shan Oct 11 '15 at 17:16
@Shan unfortunately I dont have a similar installation to yours so I cannot try further, but I think that you might find your solution in this thread: http://stackoverflow.com/questions/3526228/vba-script-to-close-every-instance-of-excel-except-itself. – A.S.H Oct 11 '15 at 17:40

score -1 · Accepted Answer · answered Oct 13 '15 at 18:52

-1

Finally i found the problem. It was the line Application.visible=false. if i disable this it works fine but i really want to hide application.

answered Oct 13 '15 at 18:52

Shan

429
3
14
31

My code closes the workbook but not the window

4 Answers4