Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Workbook Saved except This Workbook
Hi,
After several attempts, it fails to work correctly to save all opened workbooks in a specific directory folder It works fine when there are more than one excel files opened However, in the event if there is no excel file being opened except this workbook and prompt the message " All workbooks saved " based on the following codes Dim WB As Workbook For Each WB In Workbooks If Not WB Is Nothing Then If WB.Name < ThisWorkbook.Name Then With WB .Sheets(1).Activate SheetName = .ActiveSheet.Name FileExtStr = ".xls": FileFormatNum = 56 .SaveAs Filename:=SheetName & FileExtStr, _ FileFormat:=FileFormatNum .Close SaveChanges:=True End With End If Else MsgBox "No Excel File is found" Exit Sub End If Next MsgBox "All Workbooks Saved under this Directory Folder " Any helps will be much appreciated and thanks in advance Regards Len |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Workbook Saved except This Workbook
Your code does not save/close all workbooks in a specific directory.
Your code saves/closes all OPEN workbooks except the workbook containing the code. Add this before the start of the loop... If Workbooks.Count = 1 Then MsgBox "Only 1 workbook open" Exit Sub End If -- Jim Cone Portland, Oregon USA http://www.contextures.com/excel-sort-addin.html .. .. .. "Len" wrote in message ... Hi, After several attempts, it fails to work correctly to save all opened workbooks in a specific directory folder It works fine when there are more than one excel files opened However, in the event if there is no excel file being opened except this workbook and prompt the message " All workbooks saved " based on the following codes Dim WB As Workbook For Each WB In Workbooks If Not WB Is Nothing Then If WB.Name < ThisWorkbook.Name Then With WB .Sheets(1).Activate SheetName = .ActiveSheet.Name FileExtStr = ".xls": FileFormatNum = 56 .SaveAs Filename:=SheetName & FileExtStr, _ FileFormat:=FileFormatNum .Close SaveChanges:=True End With End If Else MsgBox "No Excel File is found" Exit Sub End If Next MsgBox "All Workbooks Saved under this Directory Folder " Any helps will be much appreciated and thanks in advance Regards Len |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Workbook Saved except This Workbook
On Aug 11, 6:53*am, Len wrote:
Hi, After several attempts, it fails to work correctly to save all opened workbooks in a specific directory folder It works fine when there are more than one excel files opened However, in the event if there is no excel file being opened except this workbook and prompt the message " All workbooks saved " based on the following codes Dim WB As Workbook *For Each WB In Workbooks * * If Not WB Is Nothing Then * * * * If WB.Name < ThisWorkbook.Name Then * * * * With WB * * * *.Sheets(1).Activate * * * * SheetName = .ActiveSheet.Name * * * * FileExtStr = ".xls": FileFormatNum = 56 * * * * .SaveAs Filename:=SheetName & FileExtStr, _ * * * * * * * * * * * * FileFormat:=FileFormatNum * * * * .Close SaveChanges:=True * * * * End With * * * * End If * * Else * * MsgBox "No Excel File is found" * * Exit Sub * * End If * * Next MsgBox "All Workbooks Saved under this Directory Folder " Any helps will be much appreciated and thanks in advance Regards Len Here is one I have used for a long time that saves all and quits excel Sub CLOSE_ALL() Application.ScreenUpdating = False Application.DisplayAlerts = False For Each w In Application.Workbooks If w.Name < ThisWorkbook.Name Then w.Save End If Next w Application.Quit End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
All Workbook Saved except This Workbook
Hi Jim, Don
Thanks for your prompt reply and your codes Yes, my codes is to saves/closes all OPEN workbooks except the workbook containing the code. After work around with your codes, it works great Below is the revised codes Sub Button1_Click() Dim WB As Workbook For Each WB In Workbooks If Workbooks.Count 1 Then If WB.Name < ThisWorkbook.Name Then With WB .Sheets(1).Activate SheetName = .ActiveSheet.Name FileExtStr = ".xls": FileFormatNum = 56 .SaveAs Filename:=SheetName & FileExtStr, _ FileFormat:=FileFormatNum .Close SaveChanges:=True End With End If Else MsgBox "Only 1 workbook open" Exit Sub End If Next MsgBox "All Workbooks Saved under this Directory Folder " End Sub Thanks again Cheers Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is Referenced Workbook Saved When ActiveWorkbook is Saved? | Excel Programming | |||
Worksheet saved out as new workbook | Excel Programming | |||
Changes to workbook not saved | Excel Discussion (Misc queries) | |||
Save column J of WorkBook when WorkBook is not saved. | Excel Programming | |||
How can I see a copy of a saved workbook before I saved it again? | Excel Worksheet Functions |