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

 

[daniels012]

I tried to change and delete things that are in red. I think I can eliminate WB2??? I am really trying to write code???? Hope it works. With the changes below I think it will work from any file I call the file from not just "Proposal for XL".


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
WB1.SaveAs Filename:=strPath & strfilename, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False

On Error GoTo 0


'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 WB1.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

 

[daniels012]

Error 1004 on this line:
Set WB2 = Workbooks.Open(Filename:=strPath2 & "Proposal for XL.xls")

The file is already open.


Michael

 

[hatman]

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

Add in the following two lines, at the bginning an end of the routine:

 Application.displayalerts = false
..code..
application.displayalerts = true

As for how to gain skill at writing code? Write more code. Been programming since I was about 8 years old... once you know the basics of how logic structures are constructed, languages are easy to learn, provided you have some sort of Help Files, or a reference manual available.

 

[hatman]

As for the rest of the dialog that you have head with Norie... I am not sure that I follow all of it. You describe saving a new file, and then you talk about modifying an existing file. Do you need this code to handle 2 separate scenarios? If so, what are the parameters for EACh scenario... this should be pretty straight-forward once defined, but I am not getting a clear sense of what you realy need.

 

[daniels012]

Thank you for the last little bit also.

Application.displayalerts = false 
..code.. 
application.displayalerts = true

Thank You,
Michael

 

[daniels012]

OK
If I open the file that was created to "Completed Proposals" directory, Then try to run this code, it won't work. It is trying to get the info from "Proposal for XL" especially if "Proposal for XL" is opened already.

Is that clearer?

Michael

 

[daniels012]

Here is what I have:

Sub Save_and_SaveSalesman()


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 & strfilename

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
Select Case WB1.Sheets("FRONT").Range("C2").Value
Case "MD"
strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename

Case "TD"
strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename

Case "DJ"
strfilename = "\\DAVEJONES\DavesProposals\" & strfilename

End Select

WB1.SaveCopyAs Filename:=strfilename

ChDir CurrPath

Application.ScreenUpdating = True

WB1.Close
End Sub

If I try to edit one of the created proposals from the directory "completed Proposals" It gives an error 1004 and says it can't do it.

Michael

 

[daniels012]

OK! I wrote code based on what you gave me and it seems to work fine!!

Sub Update_Save_and_SaveSalesman()


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
    

    Select Case WB1.Sheets("FRONT").Range("C2").Value
        Case "MD"
            strfilename = "\\MIKESRGATEWAY\MikesProposals\" & strfilename
            
        Case "TD"
            strfilename = "\\Tomsblackibm\TomsProposals\" & strfilename
            
        Case "DJ"
            strfilename = "\\DAVEJONES\DavesProposals\" & strfilename
            
    End Select
    
    WB1.Save Filename:=strfilename
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    WB1.Close
End Sub

Thank You,
Michael

 

[hatman]

Try this:

Sub Save_and_SaveSalesman()


    Dim strPath As String, strPath2 As String, CurrPath As String
    
    Dim wb1 As Workbook
    Dim WB2 As Workbook
    
    Dim flag As Boolean
    
    Dim prop As String
    
    prop = "Proposal for XL.xls"
    
    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
    
    '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
    
    flag = False
    
    For Each Item In Workbooks
    
        If Item.Name = prop Then
        
            flag = True
            
            Exit For
            
        End If
        
    Next Item
    
    If flag Then
    
        wb1.SaveCopyAs Filename:=strfilename
        
    Else
    
        '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")
        
        WB2.SaveCopyAs Filename:=strfilename
        
    End If
    
    ChDir CurrPath
    
    Application.ScreenUpdating = True
    
    wb1.Close

    
End Sub

It will perform the same as my previous code, EXCEPT if the file is already open, it will simply save the current file rather than trying to open it again...

 

[hatman]

OK! I wrote code based on what you gave me and it seems to work fine!!

I think not... you forgot this:

strfilename = Range("C6").Value & Range("O3").Value & ".xls"