VBA-Excel-另存为并删除原始工作簿 [英] VBA - Excel - Save As and delete original workbook

问题描述

从Personal.xlsb文件中，我希望VBA将当前活动的工作簿另存为用户可定义的名称在同一目录中，然后删除原始工作簿.

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

推荐答案

您需要在路径之后和文件名之前添加\.

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

更新了答案，以包含注释中的修复程序.

Updated answer to include fix from comment.

这篇关于VBA-Excel-另存为并删除原始工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！