将活动工作表保存为新工作表时的问题 [英] Issue On Saving Active Sheet as New Worksheet

问题描述

  Sub SaveSheet 
我正在尝试将第一张Excel文件保存到目录C中： （）
 ActiveSheet.Copy 
与ActiveSheet.UsedRange 
 .Copy 
 .PasteSpecial xlValues 
 .PasteSpecial xlFormats 
结束
 Application.CutCopyMode = False 
 ActiveWorkbook.SaveAsC：/&格式（范围（E19），mmm-d-yyyy）
 End Sub 
  

但我在这里有两个问题：

首先我不知道在哪里设置新的文件名？

2-我遇到以下警告消息：

，而我已经启用了宏：

你能不能让我知道如何解决这些问题？

Thansk

更新：

解决方案

.SaveAs 是

expression.SaveAs（FileName，FileFormat，Password，WriteResPassword，ReadOnlyRecommended，CreateBackup，AccessMode，ConflictResolution，AddToMru，TextCodepage，T extVisualLayout，Local）

有关详细信息，请参阅Excel帮助。当您保存工作簿时，您应该指定的最少两个参数是 FileName 和 FileFormat

如果要将文件保存为无效文件，则必须具体指定文件格式。

例如

  ActiveWorkbook.SaveAs文件名：=C：\&格式（范围（E19），mmm-d-yyyy）& .xlsx，_ 
 FileFormat：= xlOpenXMLWorkbook 
  

或更简化的方法

  Sub SaveSheet（）
 Dim FName As String 
 
 ActiveSheet.Copy 
 With ActiveSheet.UsedRange 
 .Copy 
 .PasteSpecial xlValues 
 .PasteSpecial xlFormats 
 End with 
 Application.CutCopyMode = False 
 
 FName =C ：\&格式（范围（E19），mmm-d-yyyy）& .xlsx
 
 ActiveWorkbook.SaveAs文件名：= FName，_ 
 FileFormat：= xlOpenXMLWorkbook 
 End Sub 
  

同样，如果要使用宏保存文件，上述代码将成为

  Sub SaveSheet（）
 Dim FName As String 
 
 ActiveSheet.Copy 
 With ActiveSheet.UsedRange 
 .Copy 
 .PasteSpecial xlValues 
 .PasteSpecial xlFormats 
 End With 
 Application.CutCopyMode = False 
 
 FName =C：\&格式（范围（E19），mmm-d-yyyy）& .xlsm
 
 ActiveWorkbook.SaveAs文件名：= FName，_ 
 FileFormat：= xlOpenXMLWorkbookMacroEnabled 
 End Sub 
  

以下是一些通用文件格式

  50 = xlExcel12（Excel二进制2007-2013年的工作簿，有或没有宏，xlsb）
 51 = xlOpenXMLWorkbook（2007-2013年没有宏，xlsx）
 52 = xlOpenXMLWorkbookMacroEnabled（有或没有宏在2007-2013，xlsm）
 56 = xlExcel8（Excel 2007-2013中的97-2003格式，xls）
  

I am trying to save first sheet of an excel file into the Directory C: by using this code:

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:

while I have already enabled using Macro like:

Can you please let me know how to fix these issues?

Thansk

update:

解决方案

The syntax of .SaveAs is

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 FileName and 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)

这篇关于将活动工作表保存为新工作表时的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！