vba 如何使用 Excel .SaveAs 但保留原始工作表名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23792853/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use Excel .SaveAs but retain original sheet name
提问by Wayne G. Dunn
I am working with Excel 2010, and have a userform which will support various processing options (i.e. sort by predefined ranges, get statistics, and the dreaded 'export' (SaveAs). I want to allow the user to export one of the sheets as either CSV or XLSX.
我正在使用 Excel 2010，并且有一个用户表单，它将支持各种处理选项（即按预定义范围排序、获取统计信息和可怕的“导出”（另存为）。我希望允许用户将其中一张工作表导出为CSV 或 XLSX。
The issue is when I use the SaveAs to save as a CSV, it renames the sheet to the filename I selected (minus the extension). I have searched for hours and have not found any place that provides a solution. I did find a Stack 5+ year old post, but it didn't have a solution (see How to stop renaming of excelsheets after running the save macro)
问题是当我使用 SaveAs 保存为 CSV 时，它将工作表重命名为我选择的文件名（减去扩展名）。我已经搜索了几个小时，但没有找到任何提供解决方案的地方。我确实找到了 Stack 5+ 岁的帖子，但它没有解决方案（请参阅如何在运行保存宏后停止重命名 excelsheets）
Any help would be appreciated! Thanks!
任何帮助，将不胜感激！谢谢！
Here is the code:
这是代码：
Dim ws As Excel.Worksheet
Dim strSaveName As String
Dim strThisName As String
strThisName = ThisWorkbook.Path & ThisWorkbook.Name
strSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.csv), *.csv")
Set ws = Worksheets("Export")
ws.SaveAs Filename:=strSaveName, FileFormat:=xlCSV
'I do the following TO UNDO THE RENAME <GROAN> (for now just saving as 0.csv)
Sheets("0").Name = "Export"
' Then the following is to regain my original filename since I will continue working...
ws.SaveAs Filename:=strThisName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
采纳答案by Cor_Blimey
Use ws.Copy with no args beforehand then save the new workbook
事先使用没有 args 的 ws.Copy 然后保存新工作簿
e.g. adapt your code to be like:
例如，将您的代码调整为：
Dim ws As Excel.Worksheet
Dim strSaveName As String
strSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.csv), *.csv")
Set ws = Worksheets("Export")
'Copy the ws to a new workbook
ws.Copy
'With the new wb:
With Workbooks(Workbooks.Count)
'Save and close the new workbook
.SaveAs Filename:=strSaveName, FileFormat:=xlCSV
.Close False
End With
p.s. I assume you have code to handle clicking Cancel on GetSaveAsFilename and just removed it for clarity in the question ;)
ps 我假设您有代码来处理在 GetSaveAsFilename 上单击取消，并且为了问题的清晰起见将其删除；)
