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

 

[hatman]

Are you looking for ThisWorkbook.Close??

 

[daniels012]

That may work?????
Should I put that at the end of each case statement?

Something like:
Case "MD"
Workbooks(new_file).SaveAs Filename:= _
"\\MIKESRGATEWAY\MikesProposals\" & Str(Application.Range("c3").Value) & new_file, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ThisWorkbook.Close
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
ThisWorkbook.Close

Michael

 

[daniels012]

Is this right?

Michael

 

[hatman]

I don't think so. Perhaps I am not understanding exactly what you are trying to do... or rather, I am not sure I understand exactly what your original code is NOT doing that you expect it to.

The command ThisWorkbook.close will close the workbook that is actually running the code... so this command should only be issued once, at the end of your code, assuming that your intention is to close the workbook that is running the code... the implication here is that as soon as you close the workbook that is running the code, the code stops running, so anything after that statement never gets executed.

Are you saying that Workbooks(new_file).Close is not closing the correct workbook?

Let me see if I can summarize your code:

1) Save the ActiveWorkbook in a new location under a new filename, let's call this WB1.
2) Open a different file, let's call it WB2.
3) Save WB1 again.
4) Save WB1 to yet a different location.
5) Close WB1.

Where is your code located? Is it in WB1? If it is, then your code execution stops here. Are you still trying to close WB2?

 

[daniels012]

Summarization of code:

1) Save WB1
2) Save to "Path" WB1 as new (using value in O3)
3) Re open WB1
4) Save new file
5) "Case" Send the new file to the proper salesmans computer.
Using the text in Cell C6 and the new_file name.

Hope this is clearer.

Also, Is this the way to make the name a cell value with the current files name?

& Str(Application.Range("c3").Value) & new_file, FileFormat:=xlNormal

Thank You,
Michael

 

[daniels012]

What about something like this:

new_file = ActiveWorkbook.Name
Range("F2").Select
Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
Workbooks(new_file).Save
new_file2 = Str(Application.Range("c6").Value) & new_file
Select Case Workbooks(new_file).Sheets("FRONT").Range("C2").Value
Case "MD"
Workbooks(new_file).SaveAs Filename:= _
"\\MIKESRGATEWAY\MikesProposals\" & new_file2, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Case "TD"
Workbooks(new_file).SaveAs Filename:= _
"\\Tomsblackibm\shareddocs\TomsProposals\" & new_file2, FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End Select
Workbooks(new_file2).Close
Workbooks(new_file).Close
ChDir CurrPath
Application.ScreenUpdating = True

I have shown in red color the modifications?
Michael

 

[daniels012]

I get a debug error "13" using the code below!!!
The blue area is the part highlighted when the error occurs:

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("c6").Value) & Str(Application.Range("O3").Value), FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ThisWorkbook.Close
Case "TD"
Workbooks(new_file).SaveAs Filename:= _
"\\Tomsblackibm\shareddocs\TomsProposals\" & Str(Application.Range("c6").Value) & Str(Application.Range("O3").Value), FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False ThisWorkbook.Close
End Select
new_file2 = ActiveWorkbook.Name
Workbooks(new_file2).Close
Workbooks(new_file).Close


Michael

 

[hatman]

Either E6 or O3 is probably non-numeric. The STR() function converts a numerical value to a string, but if you pass it an alpha-numeric, or something that is already a piece of text, it will throw Error 13: Type Mismatch, since it requires a numerical value to do it's thing.

Since you are concatenating with a string anyway, VBA should coerce the cell contents to a string regardless of the format. If you want to explicitly perform the String Conversion, you should be using the CStr() function instead... it will perform the conversion whether the argumnet is numerica or text.

What is your latest code? The code in the previous 2 posts do different things, and neither one follows the summary from 3 posts back.

Here is a hint that I still use when I have a hard time making my code follow my intent: take the time to add a comment before EACH line of code, indicating what you intend that line to be doing... then step through the code a line at a tiome using F8, and verify that each command is doing EXACTLY what you intend. Also, you would be well advised to perform each string concatenation in a separate line, assigning to a different variable that is then used in the appropriate commands... make sure you have your Locals window visible when stepping through your coe, and verify that each string is EXACTLY what you want... remember that you can copy and paste from the Locals window into Notepad, or somesuch so you can verify that it is accurate.

 

[daniels012]

Here is where I am with the code:

Dim Path As String, Path2 As String, CurrPath As String
Application.ScreenUpdating = False
'First thing, save my work
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
'I then want to save my file as "Proposal" and the number in Cell O3
ActiveWorkbook.SaveAs Filename:= _
Path & "Proposal" & _
Str(Application.Range("O3").Value), FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
On Error GoTo 0
'I call this workbook "new_file"
new_file = ActiveWorkbook.Name
Range("F2").Select
'I want to open "Proposal for XL" so I can make a 2nd copy to the salesmans computer
Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
'I have to save the "new_file"
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 Workbooks("Proposal for XL.xls").Sheets("FRONT").Range("C2").Value
Case "MD"
strFileName = "\\MIKESRGATEWAY\MikesProposals\"
strFileName = strFileName & Range("C6").Value & Range("O3").Value
Workbooks("Proposal for XL.xls").SaveAs Filename:=strFileName
'Workbooks(Dir(strFileName)).Close
Case "TD"
strFileName = "\\Tomsblackibm\TomsProposals\"
strFileName = strFileName & Range("C6").Value & Range("O3").Value
Workbooks("Proposal for XL.xls").SaveAs Filename:=strFileName
'Workbooks(Dir(strFileName)).Close
End Select
Workbooks.Open Filename:=Path2 & "Proposal for XL.xls"
Workbooks(new_file).Close
ChDir CurrPath
Application.ScreenUpdating = True

End Sub

Still not working though!!
Michael