VBA:xlsm文件格式的另存为，不更改活动工作簿 [英] vba: saveas in xlsm fileformat without changing the active workbook

问题描述

我有以下代码，用于复制活动工作簿并为每个副本指定不同的名称.它运行良好，但是我确实需要从中运行代码的原始工作表才能保持活动状态.

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.

如果我改用SaveCopyAs函数，则复制的文件没有正确的文件格式(.xlsm)，并且不能像saveAs函数那样将文件格式指定为参数.

If I use the SaveCopyAs function 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 saveAs function.

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

    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

我能想到的最好的方法是，首先使用saveCopyAs进行复制，然后访问每个文件，然后使用saveAs将其保存为正确的文件格式，然后将其关闭，但这意味着需要双重工作，而我会真的很讨厌那样做.有没有更聪明的方法?

The best I can think of is to first make the copies with saveCopyAs and then access each file, save it in the correct file format with saveAs and then close it, but that means double work, and I would really hate to do that. Is there a smarter way?

推荐答案

这是我的作品. SaveCopyAs以完全相同的格式保存工作簿.

This works form me. SaveCopyAs saves the workbook in the exact same format.

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.

这篇关于VBA:xlsm文件格式的另存为，不更改活动工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！