CODEWITHSUNDEEP
vbaexcelexcel-2010
Robby
Robby

Reputation: 827

VBA - Excel - Save As and delete original workbook

From a Personal.xlsb file, I want VBA to save the current active workbook as a user-definable name in the same directory and delete the original workbook.

Below is my code. It has two problems. One, it saves the workbook in My Documents folder for some reason. The active workbook is not in My Documents. It's in a folder in a completely different drive. Two, it throws a "File not found" error.

Sub RenameFile()
Dim thisWb As Workbook
Set thisWb = ActiveWorkbook
MyOldName = ActiveWorkbook.Name
MyNewName = InputBox("What do you want to rename the file as?", "Rename", ActiveWorkbook.Name)

ActiveWorkbook.SaveAs Filename:=thisWb.Path & MyNewName
Kill MyOldName
End Sub

Upvotes: 1

Views: 8535

Answers (2)

tjb1
tjb1

Reputation: 757

You need to include a \ after path and before filename.

Sub RenameFile()
Dim thisWb As Workbook
Set thisWb = ActiveWorkbook
MyOldName = ActiveWorkbook.FullName
MyNewName = InputBox("What do you want to rename the file as?", "Rename", ActiveWorkbook.Name)

ActiveWorkbook.SaveAs Filename:=thisWb.Path & "\" & MyNewName
Kill MyOldName
End Sub

Edit: Updated answer to include fix from comment.

Upvotes: 3

user2906801
user2906801

Reputation:

So I have some code that I use to auto save files (to a directory specified in the code) and then deletes a particular file type out of said folder. (I use it to save a .xlsx and delete .csv)

'Saves file to specified location
    ActiveWorkbook.SaveAs filename:="C:\Desktop\Testing\Testing File " _
    & Format(Now() - 1, "DD.MM.YY") & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    On Error Resume Next
    Kill "C:\Desktop\Testing*.csv*"
    On Error GoTo 0

This is the code that I used that is within a longer VBA module, but you could incorporate this into existing VBA code

Take note that that currently saves the file as Testing with the day before the current system date in the name, such as "Testing 30.10.16"

Upvotes: 0

Related Questions