Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help
I have created an Excel Template and a macro that save it under a new name
the problem is that everytime it saves under new name the new book stays open as well as the template; i need the template to stay open and the book created close after saving; here is what i have so far Sub PrintAndClear() ' ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' ' Keyboard Shortcut: Ctrl+Shift+Q ' response = MsgBox("Do you want to Save this to a new file?" _ , vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then End ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub i tried adding ActiveWorkbook.Close after the print out but it does not work it closes the book and the template but leaves Excel running your help will be greatly appreciated -- Sigmund |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help
Are you sure that you end up with the template still open and the newly saved
workbook still open? I would have guessed that you'd see the newly saved workbook, but the template would have "disappeared". But if you want to save (and close) a workbook and keep the template a template, try using .SaveCopyAs instead of .saveAs. ps. I'm not sure what that workbooks.open line is doing. Could that be causing part of the confusion? Sigmund wrote: I have created an Excel Template and a macro that save it under a new name the problem is that everytime it saves under new name the new book stays open as well as the template; i need the template to stay open and the book created close after saving; here is what i have so far Sub PrintAndClear() ' ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' ' Keyboard Shortcut: Ctrl+Shift+Q ' response = MsgBox("Do you want to Save this to a new file?" _ , vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then End ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub i tried adding ActiveWorkbook.Close after the print out but it does not work it closes the book and the template but leaves Excel running your help will be greatly appreciated -- Sigmund -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help
Changing to SaveCopyAs worked fine but know it gives me message
Calculation Sheet 2008-WSO 9Name of the Template) is already Open, reopening will cause any changes you made to be discarded. do you want to reopen (Name of the Template) Yes - No is there a way that the macro can answer yes to that without user intervention? if so what is the VBA command? Thanks again -- Sigmund "Sigmund" wrote: I have created an Excel Template and a macro that save it under a new name the problem is that everytime it saves under new name the new book stays open as well as the template; i need the template to stay open and the book created close after saving; here is what i have so far Sub PrintAndClear() ' ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' ' Keyboard Shortcut: Ctrl+Shift+Q ' response = MsgBox("Do you want to Save this to a new file?" _ , vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then End ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub i tried adding ActiveWorkbook.Close after the print out but it does not work it closes the book and the template but leaves Excel running your help will be greatly appreciated -- Sigmund |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help
Hi Sigmund:
I think that you are half way to achive what you want in a fairly complete manner. The reason why this will not work is because when you have the Template open, and you save it as the new file, the fact is that you still have the same file, just with a new name. So, you don't have the other file any more. Two choices here, either you continue this road and then save the file, close the file and open the template again, or what I am posting here. As you will see, this is a little more structured, but with a huge advantage, you don't have to be worry where the file is going to be save, or pull as once is open, the program will always know where the file was originally open, so it will know where to save it. Unless otherwise, this is a good and stable manner to handle this kind of projects. Ok, please copy the following and substitute your current macro. Let me know how does it work. Suggestion: Copy the instruction (line) below, in the "This Workbook" part, wherethe macros and modules are, but in the side of the sheets. This will guarantee, that even if the program crash, anywhere, as soon you click the mouse, it will pick up all the public variables. Here is the line Location_Identification Argy! =================== Program ====================== Public Actual_Book As String Public Actual_Path As String Public Location As String Public Location_1 As String Public Name As String Function Saving() ' Macro recorded 3/3/2002 by Argemiro Calderon Location_Identification Windows(Actual_Book).Activate Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=Location ActiveWorkbook.SaveAs Filename:=Location_1 Application.DisplayAlerts = True Windows(Actual_Book).Activate End Function Function Location_Identification() Actual_Book = ActiveWorkbook.Name Actual_Path = ActiveWorkbook.Path Location = Actual_Path & "\" & Name Location_1 = Actual_Path & "\" & Actual_Book End Function Sub PrintAndClear() ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' Keyboard Shortcut: Ctrl+Shift+Q Actual_Book = ActiveWorkbook.Name response = MsgBox("Do you want to Save this to a new file?", vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then MsgBox ("The name of the file is in blank. Please check and hit ok to continue") End End If Saving ' ActiveWorkbook.SaveAs Filename:="c:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True '"I think this line is not needed" Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub "Sigmund" wrote: Changing to SaveCopyAs worked fine but know it gives me message Calculation Sheet 2008-WSO 9Name of the Template) is already Open, reopening will cause any changes you made to be discarded. do you want to reopen (Name of the Template) Yes - No is there a way that the macro can answer yes to that without user intervention? if so what is the VBA command? Thanks again -- Sigmund "Sigmund" wrote: I have created an Excel Template and a macro that save it under a new name the problem is that everytime it saves under new name the new book stays open as well as the template; i need the template to stay open and the book created close after saving; here is what i have so far Sub PrintAndClear() ' ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' ' Keyboard Shortcut: Ctrl+Shift+Q ' response = MsgBox("Do you want to Save this to a new file?" _ , vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then End ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub i tried adding ActiveWorkbook.Close after the print out but it does not work it closes the book and the template but leaves Excel running your help will be greatly appreciated -- Sigmund |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro Help
Why reopen the workbook?
Just remove that line from your code. And just to make sure... You're using an existing .xls as a template file. You're not using a .xlt (a real template file), are you? ======= If you want to reinitialize any of the cells in the "template" file after you've done the .savecopyas, just add a routine that empties/resets to 0 (or whatever you need to do) all the input cells. Sigmund wrote: Changing to SaveCopyAs worked fine but know it gives me message Calculation Sheet 2008-WSO 9Name of the Template) is already Open, reopening will cause any changes you made to be discarded. do you want to reopen (Name of the Template) Yes - No is there a way that the macro can answer yes to that without user intervention? if so what is the VBA command? Thanks again -- Sigmund "Sigmund" wrote: I have created an Excel Template and a macro that save it under a new name the problem is that everytime it saves under new name the new book stays open as well as the template; i need the template to stay open and the book created close after saving; here is what i have so far Sub PrintAndClear() ' ' PrintAndClear Macro ' Macro recorded 9/10/2008 by me ' ' Keyboard Shortcut: Ctrl+Shift+Q ' response = MsgBox("Do you want to Save this to a new file?" _ , vbYesNo, "Save As") If response = 6 Then Name = InputBox("Enter a Filename", "Get Filename") If Name = "" Then End ActiveWorkbook.SaveAs Filename:="P:\" & Name & ".xls" End If ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True Workbooks.Open Filename:="P:\Calculations Sheet-2008 WSO.xls" Range("E6").Select End Sub i tried adding ActiveWorkbook.Close after the print out but it does not work it closes the book and the template but leaves Excel running your help will be greatly appreciated -- Sigmund -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help to update macro to office 2007 macro enabled workbook | Excel Discussion (Misc queries) | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) |