vba：另存为 xlsm 文件格式而不更改活动工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25213848/
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
vba: saveas in xlsm fileformat without changing the active workbook
提问by Miqi180
I have the following code which makes copies of the active workbook and gives each copy a different name. It works well, BUT I really need the original worksheet from which the code is run to stay active.
我有以下代码，它制作活动工作簿的副本并为每个副本提供不同的名称。它运行良好，但我真的需要运行代码的原始工作表以保持活动状态。
If I use the SaveCopyAsfunction instead, the copied files do not have the correct file format (.xlsm), and you cannot specify the file format as a parameter as in the saveAsfunction.
如果我改用该SaveCopyAs函数，则复制的文件没有正确的文件格式 (.xlsm)，并且您不能像在saveAs函数中那样将文件格式指定为参数。
http://msdn.microsoft.com/en-us/library/bb178003%28v=office.12%29.aspx
http://msdn.microsoft.com/en-us/library/bb178003%28v=office.12%29.aspx
http://msdn.microsoft.com/en-us/library/office/ff841185%28v=office.15%29.aspx
http://msdn.microsoft.com/en-us/library/office/ff841185%28v=office.15%29.aspx
Sub makeCopies()
Dim name As Range, team As Range
Dim uName As String, fName As String, fFormat As String
Dim location as string, nName as string
location ="c:\test\"
nName = "Test - Team "
Set team = Names("Team").RefersToRange
For Each name In team
uName = nName & name.Value
fName = location & uName
fFormat = ThisWorkbook.FileFormat
ActiveWorkbook.SaveAs FileName:=fName, FileFormat:=fFormat
Next name
End sub
The best I can think of is to first make the copies with saveCopyAsand then access each file, save it in the correct file format with saveAsand then close it, but that means double work, and I would really hate to do that. Is there a smarter way?
我能想到的最好的方法是首先制作副本，saveCopyAs然后访问每个文件，将其保存为正确的文件格式，saveAs然后关闭它，但这意味着双重工作，我真的很讨厌这样做。有没有更聪明的方法？
采纳答案by L42
This works form me. SaveCopyAssaves the workbook in the exact same format.
这对我有用。SaveCopyAs以完全相同的格式保存工作簿。
Sub makeCopies()
Dim name As Range, team As Range
Dim uName As String, fName As String, tempname As String
Dim location As String, nName As String
location = "C:\Test\"
nName = "Test - Team "
Set team = ThisWorkbook.Names("Team").RefersToRange
For Each name In team
uName = nName & name.Value
fName = location & uName & "." & _
Split(ThisWorkbook.FullName, ".") _
(UBound(Split(ThisWorkbook.FullName, ".")))
ThisWorkbook.SaveCopyAs fName
Next name
End Sub
Is this what you're trying? Tried and tested.
这是你正在尝试的吗？尝试和测试。
