Need to add to code: Close file

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
Here is the code I am using now:

Code:
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664

ADVERTISEMENT

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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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?
Code:
& Str(Application.Range("c3").Value) & new_file, FileFormat:=xlNormal

Thank You,
Michael
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219

ADVERTISEMENT

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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Forum statistics

Threads
1,136,878
Messages
5,678,305
Members
419,753
Latest member
Sallylwy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top