| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SaveAs without closing original workbook
Hi All,
I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
I forgot to mention that the workbook to be cloned contains the cloning Sub,
so Thisworkbook is to be cloned! Stefio Stefi ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
Adapt something like the following, if testing in a new wb first run Sub
origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
Thanks Peter, I'll test it tomorrow!
Stefi Peter T ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
Use SaveCopyAs
See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi Peter T ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
Much better, I didn't think of that!
Regards, Peter T "Ron de Bruin" wrote in message ... Use SaveCopyAs See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi "Peter T" ezt rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
SaveAs without closing original workbook
Thanks Ron and Peter! Peter's solution works, but Ron's one is really much
nicer! Stefi Peter T ezt *rta: Much better, I didn't think of that! Regards, Peter T "Ron de Bruin" wrote in message ... Use SaveCopyAs See the VBA help -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Stefi" wrote in message ... Thanks Peter, I'll test it tomorrow! Stefi "Peter T" ezt *rta: Adapt something like the following, if testing in a new wb first run Sub origName() Sub origName() ThisWorkbook.SaveAs "TestOrig.xls" End Sub Sub test() Dim sPath As String Dim bKeepOpen Const cOrigName As String = "TestOrig.xls" ' << change to the default name bKeepOpen = True If ThisWorkbook.Name = cOrigName Then ThisWorkbook.Save sPath = ThisWorkbook.Path & "\" & _ Replace(ThisWorkbook.Name, ".xls", "", , , vbTextCompare) & "_" Application.DisplayAlerts = False For i = 1 To 3 ThisWorkbook.SaveAs sPath & MonthName(i, True) & ".xls" Next ThisWorkbook.SaveAs cOrigName If bKeepOpen Then For i = 1 To 3 Workbooks.Open sPath & MonthName(i, True) & ".xls" Next End If End If Application.DisplayAlerts = True End Sub Save with as many names as you need, then re-save with the original name, optionally reopen the others. Regards, Peter T "Stefi" wrote in message ... I forgot to mention that the workbook to be cloned contains the cloning Sub, so Thisworkbook is to be cloned! Stefio "Stefi" ezt *rta: Hi All, I want to clone a workbook in several copies with different file names, and also I want to keep the original workbook open during the process and activate it again at the end of the process. SaveAs works, but closes the original. Thanks, Stefi |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| SaveAs closing file without firing event WorkBook_beforeclose | Excel Programming | |||
| copying worksheets to a new workbook without formulae referencing original workbook | Excel Programming | |||
| Keep original wkbk open after SaveAs? | Excel Programming | |||
| Copy file and closing original | Excel Programming | |||
| closing excel after closing a workbook | Excel Programming | |||
Linear Mode
