Save, then close

[daniels012]

I have code now to save and copy a file based on info on my form.
It stays open as well as opens


Sub Print_And_Save()
'
' Print_And_Save Macro
' Prints Proposal, saves proposal, then saves as to Completed Proposals folder.
ActiveWorkbook.Save
Path = "C:\WINDOWS\Personal\Completed Proposals\"
Path2 = "C:\WINDOWS\Personal\Surface Systems\"
ActiveWorkbook.SaveAs Filename:= _
Path & "Proposal" & _
Str(Application.Range("N2").Value), FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("F2").Select
Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
End Sub

What do I need to do (and where) to close the workbook I just created? I really want to Save As and Close!
I want to leave the "Proposal for XL" file open ready do work in.

Thank You,
Michael

 

[erik.van.geit]

"It doesn't work" doesn't tell much about what happens.

What is your code doing?

When you save and directly close the workbook no intermediate changes can have been made to it, unless by a BeforeClose-module, which will invoke the "Save changes?" Message.

are we going into the right direction?
kind regards,
Erik

 

[daniels012]

This is the code I have

Sub Print_And_Save()
'
' Print_And_Save Macro
' Prints Proposal, saves proposal, then saves as to Completed Proposals folder.
'

'
    ActiveWorkbook.Save
    Path = "C:\WINDOWS\Personal\Completed Proposals\"
    Path2 = "C:\WINDOWS\Personal\Surface Systems\"
    ActiveWorkbook.SaveAs Filename:= _
        Path & "Proposal" & _
Str(Application.Range("N2").Value), FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    Range("F2").Select
    Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
End Sub

If I insert "ActiveWorkbook.Close " Before Range("F2") or after that, It closes both workbooks.
In my original code above, I save as a new file and the new file created stays open, then the last bit of code reopens "Proposal for XL"
Which opens both of them!! I want the newly created file to close after it is saved as. If I add the code you suggest, The code will not Open my "Proposal for XL" file??

Not sure what I need to do?
Hope this is more clear!

Michael

 

[daniels012]

This is the code I have

Sub Print_And_Save()
'
' Print_And_Save Macro
' Prints Proposal, saves proposal, then saves as to Completed Proposals folder.
'

'
    ActiveWorkbook.Save
    Path = "C:\WINDOWS\Personal\Completed Proposals\"
    Path2 = "C:\WINDOWS\Personal\Surface Systems\"
    ActiveWorkbook.SaveAs Filename:= _
        Path & "Proposal" & _
Str(Application.Range("N2").Value), FileFormat:=xlNormal _
        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    Range("F2").Select
    Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
End Sub

If I insert "ActiveWorkbook.Close " Before Range("F2") or after that, It closes both workbooks.
In my original code above, I save as a new file and the new file created stays open, then the last bit of code reopens "Proposal for XL"
Which opens both of them!! I want the newly created file to close after it is saved as. If I add the code you suggest, The code will not Open my "Proposal for XL" file??

Not sure what I need to do?
Hope this is more clear!

Michael

 

[erik.van.geit]

Did you make the workbook yourself?
You didn't answer the question if there was a Workbook_before_close event somewhere, but the solution might occur here...

let's pass through the code: you can do this hitting "F8"
supposing "proposal" is the only open workbook
ActiveWorkbook.Save
Path = "C:\WINDOWS\Personal\Completed Proposals\"
Path2 = "C:\WINDOWS\Personal\Surface Systems\"
ActiveWorkbook.SaveAs Filename:= _
Path & "Proposal" & _
Str(Application.Range("N2").Value), FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
still one workbook open but now with other name

IF YOU CLOSE HERE THE CODE WILL END ==> nothing will happen from here as if the code wasn't there!!! unless you use an OnTime event to open "Proposal again"
see: http://www.mrexcel.com/board2/viewtopic.php?t=125575&highlight=close+open
Range("F2").Select
Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
IF YOU DIDN'T CLOSE NOW YOU'VE GOT TWO WORKBOOKS OPEN

this will get you furhter isn't it?
kind regards,
Erik

 

[erik.van.geit]

Sorry, I forgot the simple solution:

after SAVEAS give the workbook a name
new_file = Activeworkbook.Name
then open again "Proposal"
then close the other
Workbooks(new_file).close

kind regards,
Erik

PS this is better then the Ontime event

 

[daniels012]

after SAVEAS give the workbook a name
new_file = Activeworkbook.Name
then open again "Proposal"
then close the other
Workbooks(new_file).close

The Save as gives the workbook a name, is there a way to use this name automatically? Or did I speak to soon? I will try the code you provided.

Thank You,
Michael

 

[daniels012]

Forget my last Reply!!!
IT WORKED!!!!!!!


Thank You sooooooooooo much!
Michael