Kill file and save as

Pixelpusher

Board Regular
Joined
Feb 15, 2005
Messages
92
This has been buging me for a few days I realy need to resolve this one.
I have the code below to save the active workbook to a location as a defined name, what I need is to add code to deleat(Kill) the original file and close new saves workbook without a promt.

I have read lots of threads on this but with errors one deleated my file with the macros attached thanks.

'=====================================================
Sub ArcivePDF()

Dim Response As Integer
' Displays a message box with the yes and no options.
Response = MsgBox(Prompt:="Please check for any empty RED cells.Are you sure you want to save as final CDS [.PDF].'Yes' or 'No'.", Buttons:=vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
Dim sPath As String
'Change to CDSARCIVE location.
sPath = "F:\PGregory\EDEC\CDSARCIVE\"
On Error Resume Next
ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B6").Value & ".xls"
If Err.Number <> 0 Then
MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered"
Err.Clear
End If
Else
' The no button was selected.
MsgBox "You selected 'No'."
End If
End Sub
=====================================================
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Does this work?
Code:
Sub ArcivePDF()
Dim Response As Integer
Dim sPath As String
Dim strOldFileName

    ' Displays a message box with the yes and no options.
    Response = MsgBox(Prompt:="Please check for any empty RED cells.Are you sure you want to save as final CDS [.PDF].'Yes' or 'No'.", Buttons:=vbYesNo)
    ' If statement to check if the yes button was selected.
    If Response = vbYes Then

        'Change to CDSARCIVE location.
        sPath = "F:\PGregory\EDEC\CDSARCIVE\"
        strOldFileName = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
        
        On Error Resume Next
        
        ActiveWorkbook.SaveAs sPath & ActiveSheet.Range("B6").Value & ".xls"
        
        Kill strOldFileName
        
        If Err.Number <> 0 Then
            MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered"
            Err.Clear
        End If
        
    Else
    ' The no button was selected.
    MsgBox "You selected 'No'."
    End If
    
End Sub
 

Pixelpusher

Board Regular
Joined
Feb 15, 2005
Messages
92
Kill file and save as (Plus)

Your a star Norie it works as I explaned it but I have an added problem if the file is a template the code shows the MsgBox "File not saved!" & vbLf & "Check if all referance Numbers are Entered" but infact the file is saved.
maybe I should explane what I am doing.Buy the way what is the connection with Lima?.

I have a number of master workbooks in template format (*XLt) the end user opens a template from a list in a userform, works on it then saves it as a nomal excel file with the macro prevously posted it will then be converted later to a PDF. now if for any reason the sheet is not completed then it is saved in a differant folder (Work in Progress) by means of another macro.
It then can be opened again completed and saved with the macro previosly posted. So its only if the sheet is opened from the work in progress folder that needs to be killed when its saved.the file name will always have "WIP" in front of it Any ideas wellcome. :confused:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,525
Messages
5,602,177
Members
414,510
Latest member
mande358

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