Code to move excel file from one folder to another folder

[rwilard]

Hello, I have been unable to find a code that allows me to move the current workbook from one folder into another so I thought of the idea of a save as that saves in the desired folder but also kills the file in the current folder. I have gotten the save to work but not the kill. The file is present in both folders, can someone help me?

path = "I:\AAA-PURCHASE ORDERS\test1"
path2 = "I:\AAA-PURCHASE ORDERS\test2"
filename1 = "PO#_" & Range("H4").Text & "_" & Range("C10").Text & "_" & Range("H7").Text & "_" & Range("F49").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=path2 & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
MsgBox "File Saved"
Application.DisplayAlerts = True



Kill path & filename1
Application.ActiveWorkbook.Close False

 

[rwilard]

path = "I:\AAA-PURCHASE ORDERS\test1\filename1.xlsm"
path2 = "I:\AAA-PURCHASE ORDERS\test2\filename1.xlsm"
filename1 = "PO#_" & Range("H4").Text & "_" & Range("C10").Text & "_" & Range("H7").Text & "_" & Range("F49").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=path2 & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
MsgBox "File Saved"
Application.DisplayAlerts = True




Kill path & filename1

 

[Yongle]

Please read post#3, test the suggestion and report back

 

[John_w]

Hello, I have been unable to find a code that allows me to move the current workbook from one folder into another so I thought of the idea of a save as that saves in the desired folder but also kills the file in the current folder. I have gotten the save to work but not the kill. The file is present in both folders, can someone help me?

path = "I:\AAA-PURCHASE ORDERS\test1"
path2 = "I:\AAA-PURCHASE ORDERS\test2"
filename1 = "PO#_" & Range("H4").Text & "_" & Range("C10").Text & "_" & Range("H7").Text & "_" & Range("F49").Text
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=path2 & filename1 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
MsgBox "File Saved"
Application.DisplayAlerts = True



Kill path & filename1
Application.ActiveWorkbook.Close False

Try this:

Public Sub Save_and_Delete()

    Dim saveInPath As String, newFilePath As String
    
    saveInPath = "I:\AAA-PURCHASE ORDERS\test2\"
    
    newFilePath = saveInPath & "PO#_" & Range("H4").text & "_" & Range("C10").text & "_" & Range("H7").text & "_" & Range("F49").text & ".xlsm"
    
    With ThisWorkbook
        Application.DisplayAlerts = False
        .SaveCopyAs Filename:=newFilePath
        Application.DisplayAlerts = True
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Saved = True
    End With
    
    MsgBox "Saved " & newFilePath
        
    Application.Quit

End Sub

 

[arcaidius]

Wow I have been trying to do this same thing all day long, and finally it works. The last code works, Thanks!

 

[John_w]

Small change to post 13. Move the .Saved = True line to immediately above the .ChangeFileAccess xlReadOnly line.

 

[jim may]

Small change to post 13. Move the .Saved = True line to immediately above the .ChangeFileAccess xlReadOnly line.

Nice code John_w

Can you state the reason for your change request for all to know?

Jim

 

[John_w]

It prevents a "Do you want to save changes before switching file status?" prompt.

 

[jim may]

Great! - Class dismissed! Jim