使用VBA将1300个随机25个文件复制到另一个文件夹 [英] Copy random 25 files from 1300 to another folder with VBA

问题描述

我在服务器上有1300个excel文件，其中包含收入.我需要将这些收入与一个数据透视文件进行比较，以确保实际2个文件中的收入相同.因为它在服务器上，所以从服务器打开所有文件都非常慢，这就是为什么我要先将其中的一个示例(25个excel文件)复制到我的compter，然后从该文件夹运行比较宏.但是我想使复制过程自动化，因此我需要以某种方式随机选择其中25个文件，然后将其复制到另一个文件夹.我有一个代码可以将所有文件从一个文件夹复制到另一个文件夹，但是我需要对其进行随机选择.谢谢.

I have 1300 excel files on a server, with revenues in them. I need to compare these revenues with one pivot file to make sure the revenues are the same in the actual 2 files. Because it is on a server, opening all of them from a server would be pretty slow, thats why I want to copy a sample of them (25 excel files) to my compter first, and then run my comparison macro from this folder. But I want to make the copying process automatized, so I somehow need to select randomly 25 of these files, and then copy it to an other folder. I have a code to copy all of the files from one folder to another, but I need the random selection to it. Thanks.

 Sub Copy_Folder()

 Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
FromPath = "C:\Users\NagyI2\Documents\Macro testing"
ToPath = "C:\Users\NagyI2\Documents\Copy test"

If Right(FromPath, 1) = "\" Then
    FromPath = Left(FromPath, Len(FromPath) - 1)
End If

If Right(ToPath, 1) = "\" Then
    ToPath = Left(ToPath, Len(ToPath) - 1)
End If

Set FSO = CreateObject("scripting.filesystemobject")

If FSO.FolderExists(FromPath) = False Then
    MsgBox FromPath & " doesn't exist"
    Exit Sub
End If

FSO.CopyFolder Source:=FromPath, Destination:=ToPath

End Sub

推荐答案

文件夹-对象的 files -集合提供了该文件夹中的文件列表.但是，您不能按索引访问文件之一，而只能按名称访问.因此，以下代码首先创建一个包含所有文件名称的数组.然后，在第二个循环中，随机创建文件索引，然后将文件复制到目标文件夹.

The files-collection of a folder-object gives a list of files in that folder. However, you cannot access to one of the files by index, just by name. So the following code creates first an array with the names of all files. Then, in a second loop, a file index is created by random, and the file is copied to the destination folder.

Dim FSO As Object, folder a Object, file as Object
Set folder = fso.GetFolder(FromPath)
Dim fList() As String, i As Long
ReDim fList(1 To folder.Files.Count)

For Each file In folder.Files
    i = i + 1
    fList(i) = file.Name
Next file

Dim copyCount As Long, fIndex As Long
copyCount = 0
Do While copyCount < 25 And copyCount < folder.Files.Count
    fIndex = Int(Rnd * folder.Files.Count) + 1
    If fList(fIndex) <> "" Then
        Set file = folder.Files(CStr(fList(fIndex)))
        file.Copy ToPath, True
        fList(fIndex) = ""    '  Mark this file as copied to prevent that it is picked a 2nd time
        copyCount = copyCount + 1
    End If
Loop

这篇关于使用VBA将1300个随机25个文件复制到另一个文件夹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！