创建文件名称略有不同的同一文件的多个副本的最快方法 [英] Quickest way to create multiple copies of the same file with slightly different file names

问题描述

我目前正在执行以下操作:

I'm currently doing the following:

Global myNames() As Variant

Sub createEmptyTemplates(ByVal destPath As String, ByVal tempPath As String)

':: this is just to create a load of copies of a template

Dim aName
For Each aName In myNames()

    Dim myDest As String
    myDest = destPath & "\" & "Copy of template named - " & aName & ".xlsx"

    FileSystem.FileCopy tempPath, myDest

Next aName 

End Sub

在此子例程之前，变体数组myNames()用200个变体/字符串填充.它使用的模板是一个非常复杂的excel文件.

Before this sub-routine is called the variant array myNames() is filled with 200 variant/strings. The template that it is using is quite a complex excel file.

创建所有文件后，将继续执行进一步的例程，该例程将打开每个导入相关数据的文件.

After creating all the files it than proceeds to a further routine that opens each file importing relevant data.

上面的例程不是很快-总共可能需要5分钟.有没有更有效的方式来创建所有这些文件副本?

The above routine is not so quick - maybe 5mins in total. Is there a more efficient way of creating all these file copies?

推荐答案

经过测试，并具有相当令人惊讶的结果，表明使用filesystemObject比我测试的其他两种方法更好.

Tested and have reasonably surprising results that show that using a filesystemObject is way better than the other two approaches I tested.

模板约为2000KB.我已将运行限制为仅模板的4个副本.

The template is approx 2000KB. I have limited the run to just 4 copies of the template.

Version1 FileSystem.FileCopy:创建每个副本的时间:

Version1 FileSystem.FileCopy: Times to create each copy:

• 2.737秒
• 2.722s
• 2.406s
• 2.496秒

代码:

Global myNames() As Variant

Sub createEmptyTemplates(ByVal destPath As String, ByVal templateFullPathName As String)

':: this is just to create a load of copies of a template

Dim aName
For Each aName In myNames()

    Dim myDest As String
    myDest = destPath & "\" & "Copy of template named - " & aName & ".xlsx"

    FileSystem.FileCopy tempPath, myDest

Next aName 

End Sub

Version2 Scripting.FileSystemObject.CopyFile(具有早期绑定的Ref):创建每个副本的时间:

Version2 Scripting.FileSystemObject.CopyFile (with early binding Ref): Times to create each copy:

• 0.244秒
• 0.084秒
• 0.093秒
• 0.080秒

代码:

 Global myNames() As Variant

    Sub createEmptyTemplates(ByVal destPath As String, ByVal templateFullPathName As String)

    ':: this is just to create a load of copies of a template

    Dim fso As Scripting.FileSystemObject
    Set fso = New Scripting.FileSystemObject

    Dim aName
    For Each aName In myNames()

        Dim myDest As String
        myDest = destPath & "\" & "Copy of template named - " & aName & ".xlsx"

        fso.CopyFile _
           Source:=templateFullPathName, _
           Destination:=myDest

    Next aName 

    If Not (fso Is Nothing) Then Set fso = Nothing
    End Sub

版本3 wbObjVar.SaveCopyAs:创建每个副本的时间:

Version3 wbObjVar.SaveCopyAs: Times to create each copy:

• 3.348秒
• 3.740秒
• 3.179s
• 3.418秒

代码:

Global myNames() As Variant

Sub createEmptyTemplates(ByVal destPath As String, ByVal templateFullPathName As String)

':: this is just to create a load of copies of a template

Dim t As Excel.Workbook
Set t = Excel.Workbooks.Open(templateFullPathName, , False, , , , True)

Dim aName
For Each aName In myNames()
    t.SaveCopyAs  destPath & "\" & "Copy of template named - " & aName & ".xlsx"
Next aName 

If Not (fso Is Nothing) Then Set fso = Nothing
End Sub

这篇关于创建文件名称略有不同的同一文件的多个副本的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！