vba 将活动工作表另存为新工作表的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20632514/
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
Issue On Saving Active Sheet as New Worksheet
提问by Behseini
I am trying to save first sheet of an excel file into the Directory C: by using this code:
我正在尝试使用以下代码将 Excel 文件的第一张表保存到目录 C: 中：
Sub SaveSheet()
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
Application.CutCopyMode = False
ActiveWorkbook.SaveAs "C:/" & Format(Range("E19"), "mmm-d-yyyy")
End Sub
but I am having two issues here:
First of all I do not know where to SET the new file Name?
2- I am encountering with following warning message:
但我在这里有两个问题：
首先我不知道在哪里设置新文件名？
2- 我遇到以下警告消息：
while I have already enabled using Macro like:
虽然我已经启用了使用宏，例如：
Can you please let me know how to fix these issues?
你能告诉我如何解决这些问题吗？
Thansk
谢克
update:
更新：
回答by Siddharth Rout
The syntax of .SaveAsis
的语法.SaveAs是
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
expression.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
Refer to Excel Help for more details. When you are saving a workbook, the minimum of two parameters that you should specify is FileNameand FileFormat
有关详细信息，请参阅 Excel 帮助。保存工作簿时，应指定的两个参数中的最小值是FileName和FileFormat
If you want to save the file as macro free file then you will have to specify the file format specifically.
如果要将文件另存为无宏文件，则必须专门指定文件格式。
For example
例如
ActiveWorkbook.SaveAs Filename:="C:\" & Format(Range("E19"), "mmm-d-yyyy") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook
or a more simplified approach
或更简单的方法
Sub SaveSheet()
Dim FName As String
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
Application.CutCopyMode = False
FName = "C:\" & Format(Range("E19"), "mmm-d-yyyy") & ".xlsx"
ActiveWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlOpenXMLWorkbook
End Sub
Similarly if you want to save the file with macros the above code becomes
同样，如果你想用宏保存文件，上面的代码变成
Sub SaveSheet()
Dim FName As String
ActiveSheet.Copy
With ActiveSheet.UsedRange
.Copy
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
Application.CutCopyMode = False
FName = "C:\" & Format(Range("E19"), "mmm-d-yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=FName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled
End Sub
Here are some commnon file formats
以下是一些常见的文件格式
50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)
