Need to add to code: Close file

[daniels012]

Here is the code I am using now:

Dim Path As String, Path2 As String, CurrPath As String
    Application.ScreenUpdating = False
    ActiveWorkbook.Save
    CurrPath = ActiveWorkbook.Path
    Path = "C:\Documents and Settings\Owner\My Documents\Completed Proposals\"
    Path2 = "C:\Documents and Settings\Owner\My Documents\Surface Systems\"
    On Error Resume Next
    ActiveWorkbook.SaveAs Filename:= _
                          Path & "Proposal" & _
                          Str(Application.Range("O3").Value), FileFormat:=xlNormal _
                        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                          CreateBackup:=False
    On Error GoTo 0
    new_file = ActiveWorkbook.Name
    Range("F2").Select
    Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
    Workbooks(new_file).Save
    Select Case Workbooks(new_file).Sheets("FRONT").Range("C2").Value
    Case "MD"
        Workbooks(new_file).SaveAs Filename:= _
                          "\\MIKESRGATEWAY\MikesProposals\" & Str(Application.Range("c3").Value) & new_file, FileFormat:=xlNormal _
                        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                          CreateBackup:=False
    Case "TD"
        Workbooks(new_file).SaveAs Filename:= _
                          "\\Tomsblackibm\shareddocs\TomsProposals\" & Str(Application.Range("c3").Value) & new_file, FileFormat:=xlNormal _
                        , Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
                          CreateBackup:=False
    End Select
    Workbooks(new_file).Close
    ChDir CurrPath
    Application.ScreenUpdating = True

End Sub

After the code chooses the case and saves the name it's not closing this new form?

The code works fine it just won't close after the change is made.

Thank You
Michael

 

[Norie]

Michael

Is this a new thread you've started?

Why?

Did you try my suggestion regarding debugging?

Or to use SaveCopyAs?

Also, do you remember my comment regarding closing a workboook?

If you close the workbook with the code, you stop the code.

 

[daniels012]

Norie,
Yes I did the debugging. I found the stoppage happened when I got to the closed file.
I need to search and figure out how to use the SaveCopyAs. Once I have figured this out, I will try to apply it to my code.

Yes, I know that Close stops the code. So I am trying to figure a way to Close all the files at the end.

I actually started this thread awhile back and didn't realize it went through because of Internet problems on my end. So I started the other thread where I had your help. :wink: :wink:

Hatman, responded to this thread, so I am replying.

I really appreciate all the help. I just am so green when it comes to coding. I get frustrated. I am going to do some research now to try and figure this one out!!
Michael

 

[Norie]

Michael

Can't you just directly replace SaveAs with SaveCopyAs?

Then if you want to close the workbook with the code put this at the end.

ThisWorkbook.Close

 

[hatman]

1) You have named a variable "Path", which is a Property. This could be causing some unexpected behaviour.
2) You can simplify some of your code by handling the workbooks as objects... see my code changes
3) In your Select..Case, the values for C6 and O3 will be pulled from WB2 (see my code changes)... I am assuming that these values must come from WB1 instead. If you need to pull one value from WB2 and one from WB2, then move the affected line down between the Open command and the select..case, BUT make sure you use the syntax of WB1.Sheets().Range().Value to get the correct cell from the correct sheet of the correct book.

Let me know how this works for you..

Sub stuff()

    Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    
    'Application.ScreenUpdating = False
    
    Set WB1 = ActiveWorkbook
    
    'First thing, save my work
    WB1.Save
    
    CurrPath = WB1.Path
    
    'ASSUMING THAT C6 and O3 are BOTH in WB1
    'move this line HERE: only do this once, and concatenate in the Select..Case later
    'doing thsi inside the Select..Case pulls values from WB2, which might cause errors...
    strfilename = Range("C6").Value & Range("O3").Value
    
    strPath = "C:\Documents and Settings\Owner\My Documents\Completed Proposals\"
    
    strPath2 = "C:\Documents and Settings\Owner\My Documents\Surface Systems\"
    
    On Error Resume Next
    
    'I then want to save my file as "Proposal" and the number in Cell O3
    WB1.SaveAs Filename:=strPath & "Proposal" & Str(Application.Range("O3").Value), FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    
    On Error GoTo 0
    
    'I call this workbook "new_file"
    'as long as you use the WB1 object, you should not need to store the name... - PES
'    new_file = wb1.Name
    
    'you should never need to select anything... - PES
'    Range("F2").Select
    
    'I want to open "Proposal for XL" so I can make a 2nd copy to the salesmans computer
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")
    
    'I have to save the "new_file"
    'WHY??? you did this above - PES
'    Workbooks(new_file).Save
    
    'Workbooks(new_file).Close
    'Here is where i need to choose the computer for it to go to. As well as give the file a name that the salesman recognizes. C6 is customer name and O3 is the proposal number
    Select Case WB2.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename
            WB2.SaveAs Filename:=strfilename
            
        Case "TD"
            strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename
            WB2.SaveAs Filename:=strfilename
            
    End Select
    
    Workbooks.Open Filename:=strPath2 & "Proposal for XL.xls"
    
    
    WB2.Close
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True

    WB1.Close

End Sub

 

[hatman]

Norie, good point regarding SaveCopyAs... here is some tweaked code incorporating it:

Sub stuff()

    Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim WB1 As Workbook
    Dim WB2 As Workbook
    
    'Application.ScreenUpdating = False
    
    Set WB1 = ActiveWorkbook
    
    'First thing, save my work
    WB1.Save
    
    CurrPath = WB1.Path
    
    'ASSUMING THAT C6 and O3 are BOTH in WB1
    'move this line HERE: only do this once, and concatenate in the Select..Case later
    'doing thsi inside the Select..Case pulls values from WB2, which might cause errors...
    strfilename = Range("C6").Value & Range("O3").Value & ".xls"
    
    strPath = "C:\Documents and Settings\Owner\My Documents\Completed Proposals\"
    
    strPath2 = "C:\Documents and Settings\Owner\My Documents\Surface Systems\"
    
    On Error Resume Next
    
    'I then want to save my file as "Proposal" and the number in Cell O3
    WB1.SaveAs Filename:=strPath & "Proposal" & Str(Application.Range("O3").Value) & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
    
    On Error GoTo 0
    
    'I call this workbook "new_file"
    'as long as you use the WB1 object, you should not need to store the name... - PES
'    new_file = wb1.Name
    
    'you should never need to select anything... - PES
'    Range("F2").Select
    
    'I want to open "Proposal for XL" so I can make a 2nd copy to the salesmans computer
    Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")
    
    'I have to save the "new_file"
    'WHY??? you did this above - PES
'    Workbooks(new_file).Save
    
    'Workbooks(new_file).Close
    'Here is where i need to choose the computer for it to go to. As well as give the file a name that the salesman recognizes. C6 is customer name and O3 is the proposal number
    Select Case WB2.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename
            
        Case "TD"
            strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename
            
    End Select
    
    WB2.SaveCopyAs Filename:=strfilename
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close

End Sub

Daniels: if this still errors out, I need 3 things: 1)The error number, 2) the line where the error occurs, and 3) the value of each variable referenced in that line (if any). for 3) be certain that you Copy..Paste from the Locals window.

 

[daniels012]

WOW!
Hatman that worked well.
1 thing
1) It asks if I would like to save the file Yes, No, Cancel. The one created as proposal and the number. Example: "Proposal 13473"
Is there a way to avoid this message? I am good if it automatically saves it.


I really wish I could do what you have just done. How do you get so proficient writing code.
Michael

 

[daniels012]

I am sorry to be a pain
I was wondering if this is possible:

If I go and edit a proposal from the Completed Proposal Directory, This code will not work, because the code is trying to get the info from "Proposal for XL".

I talked to everyone involved. They are fine with having both files the same way. Let's save the file name as "C6 "client name" and O3 "proposal number".
then send it to completed proposal and the remote salesmans location.

How can I tweak this code to do just that?

Michael

 

[Norie]

Michael

Are you saying there are links?

 

[daniels012]

No, no links
The code that hatman provided works well.
It just won't work when I go to update the file. I don't use "Proposal for XL" to update a file. I go to Completed Proposal Dir... find the Proposal number... Then edit it there. When I hit the button that executes Hatman's code it gives an error about "Proposal for XL".

Does that make sense??

Michael

 

[Norie]

Michael

Gives an error where?