Macro to save as then delete original

[USCguy09]

the macro below when run, prompts the save as box then deletes the original file. How can I get this to resave into a specified directory without the save as prompt box?

Sub DeleteFile()

Dim MyFullName
Dim fileSaveName

MyFullName = ActiveWorkbook.FullName

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If fileSaveName <> False Then
ActiveWorkbook.SaveAs fileSaveName
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.DeleteFile (MyFullName)
End If

End Sub

 

[AlphaFrog]

The SaveAs dialog prompts the user for the new directory to save to. If you eliminate the dialog from the macro, then what directory do you want it to save to? Do you want to hard code a fixed directory path in the macro?

 

[USCguy09]

The SaveAs dialog prompts the user for the new directory to save to. If you eliminate the dialog from the macro, then what directory do you want it to save to? Do you want to hard code a fixed directory path in the macro?

well i don't know the specific directory yet but for the sake of the question, lets say i want it saved to:

V:\Personal Folders\test_location\Completed

So yes, I would want the above directory hardcoded into the macro.

 

[AlphaFrog]

Try something like this...

Sub Move_File()

    Dim NewPath As String, OldFile As String
    
    NewPath = "V:\Personal Folders\test_location\Completed\"
    
    OldFile = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs NewPath & ActiveWorkbook.Name
    Kill OldFile

End Sub

 

[USCguy09]

Try something like this...

Sub Move_File()

    Dim NewPath As String, OldFile As String
    
    NewPath = "V:\Personal Folders\test_location\Completed\"
    
    OldFile = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs NewPath & ActiveWorkbook.Name
    Kill OldFile

End Sub

thanks! this works but whenever i run the macro, it includes the text, "test_location" in the saved as file name. can i get this to just save as the current active workbook name only or maybe name it referring to a specific cell?
below is the macro i ran. your code is highlighted in red:

Sub Move_File()

Dim Pass As String
Dim Prompt As String
Dim Title As String
Dim UserPass As String

Pass = "admin"
Prompt = "Enter the password to continue"
Title = "Password Input"
UserPass = InputBox(Prompt, Title)
If UserPass <> Pass Then
Prompt = "You have entered an incorrect password"
Title = "Incorrect Password"
MsgBox Prompt, vbCritical, Title
Exit Sub
End If


[COLOR=Red]Dim NewPath As String, OldFile As String
    
    NewPath = "V:\Personal Folders\test_location\completed\"
    
    OldFile = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs NewPath & ActiveWorkbook.Name
    Kill OldFile
[/COLOR] 
MsgBox "Transfer Complete!", 64, "COMPLETED!"

End Sub

 

[AlphaFrog]

I can't get it to have test_location as part of the file name on my system. Can you give me an example of...

• File name before save as
• Filename after save as
• Desired file name

So I fully understand what you are experiencing.

Also, does this full path exist (including all the sub-directories) before you run the macro?
"V:\Personal Folders\test_location\completed\"

 

[USCguy09]

I can't get it to have test_location as part of the file name on my system. Can you give me an example of...

• File name before save as
• Filename after save as
• Desired file name

So I fully understand what you are experiencing.

Also, does this full path exist (including all the sub-directories) before you run the macro?
"V:\Personal Folders\test_location\completed\"

Nevermind, I actually figured this out through trial and error. I really appreciate your help though. Couldn't of finished this without your help.

I included "\" in one of the code line and it seemed to do the trick.
See below.
This is actually for a work order form. The file name before it saves is "Blank WO Request Form". When a person opens up the form,
they have to fill in certain required fields/cells in order for the form to be saved as a new name(when they click on a form control button). The form gets saved based on a specified cell where it concatenate some of those required fields for the save as name. So after the form control button is clicked, it saves based on that specified cell, into a specified directory then the active sheet closes. From there, another person opens up where the file got transferred to and clicks on another form control button (the macro below) and it gets transferred to another directory and deletes the original while keeping the current activesheet name.

Sub move_file()

Dim Pass As String
Dim Prompt As String
Dim Title As String
Dim UserPass As String

Pass = "admin"
Prompt = "Enter the password to continue"
Title = "Password Input"
UserPass = InputBox(Prompt, Title)
If UserPass <> Pass Then
Prompt = "You have entered an incorrect password"
Title = "Incorrect Password"
MsgBox Prompt, vbCritical, Title
Exit Sub
End If

Dim NewPath As String, OldFile As String
    
    NewPath = "V:\Personal Folders\test\test_location\Completed"
    
    OldFile = ActiveWorkbook.FullName
    [COLOR=Red]ActiveWorkbook.SaveAs NewPath & "\" & ActiveWorkbook.Name[/COLOR]
    Kill OldFile
 
MsgBox "Transfer Complete!", 64, "COMPLETED!"

End Sub

 

[AlphaFrog]

You're welcome. I'm glad it worked.

Here's a shorter version of your code.

Sub move_file()
    
    Dim OldFile As String
    
    If LCase(InputBox("Enter the password to continue.", "Password Input")) <> "admin" Then
        MsgBox "You have entered an incorrect password.", vbCritical, "Incorrect Password"
        Exit Sub
    End If
    
    OldFile = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs "V:\Personal Folders\test\test_location\Completed\" & ActiveWorkbook.Name
    Kill OldFile
    
    MsgBox "File transferred to:" & vbCr & ActiveWorkbook.FullName, , "Transfer Complete!"
    
End Sub

 

[USCguy09]

You're welcome. I'm glad it worked.

Here's a shorter version of your code.

Sub move_file()
    
    Dim OldFile As String
    
    If LCase(InputBox("Enter the password to continue.", "Password Input")) <> "admin" Then
        MsgBox "You have entered an incorrect password.", vbCritical, "Incorrect Password"
        Exit Sub
    End If
    
    OldFile = ActiveWorkbook.FullName
    ActiveWorkbook.SaveAs "V:\Personal Folders\test\test_location\Completed\" & ActiveWorkbook.Name
    Kill OldFile
    
    MsgBox "File transferred to:" & vbCr & ActiveWorkbook.FullName, , "Transfer Complete!"
    
End Sub

awesome! thanks again!