Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 323
  • Last Modified:

Select Random percentage of Text Files

I need to use VB or VBA in excel 2007 to parse a selected folder and choose a random percentage of the files within for QA. Those random files then need to be moved to a second folder for QA review. The selected filenames need to placed in a list within excel for logging purposes.

Is there an easy way to do this?
  • 3
  • 2
1 Solution
Bill PrewCommented:
So, you will specify the source folder name, the destination folder name, and the percentage of files to copy (selecting them randomly)?

I assume you mean to limit the percentage to a certain range...?  Otherwise, a "random percentage of the files" could be anywhere from 0 to 100%.  You sure you didn't mean "randomly select a percentage of the files"...?
txdolfanAuthor Commented:
Yes you will specify the source,destination, and percentage to be randomly selected. So no matter the total of the files I need to randomly select a given percentage, say 10%.
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Bill PrewCommented:
Okay, here's a vbscript approach that should be pretty close.  Save this file as a VBS file, and run like this:

cscript EE26909629.vbs "C:\EE26909629\from" "C:\EE26909629\to" "C:\EE26909629\log.csv" 10

This will select 10% of the files in "C:\EE26909629\from" randomly, copy them to "C:\EE26909629\to" and write their full paths to "C:\EE26909629\log.csv", which you can then open in Excel.

' Require variables to be defined before usage
Option Explicit

' Define some useful contants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Define all used variables here
Dim oFSO, oArgs, oFolder, oFiles, oPath(), oFile, oLogFile
Dim iIndex, iPercent, iCopy
Dim sFromFolder, sToFolder, sLogFileName, sFileToCopy

' Initialize random number generator; create filesystem object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Make sure enough parms were supplied
Set oArgs = WScript.Arguments
If (oArgs.Count < 4) Then
  WScript.Echo "ERROR: Too few parm specified." & vbCRLF
  WScript.Echo "USAGE:"
  WScript.Echo "  " & Wscript.ScriptName & " [from-folder] [to-folder] [log-file] [percent]"
End If

' Get and validate first parm (from folder path)
sFromFolder = oArgs(0)
If Right(sFromFolder, 1) <> "\" Then sFromFolder = sFromFolder & "\"
If Not oFSO.FolderExists(sFromFolder) Then
  WScript.Echo "ERROR: From folder does not exist."
End If

' Get and validate second parm (to folder path)
sToFolder = oArgs(1)
If Right(sToFolder, 1) <> "\" Then sToFolder = sToFolder & "\"
If Not oFSO.FolderExists(sToFolder) Then
  WScript.Echo "ERROR: To folder does not exist."
End If

' Get and validate third parm (CSV log file path)
sLogFileName = oArgs(2)

' Get and validate fourth parm (percent files to sample)
iPercent = oArgs(3)
If iPercent < 1 Or iPercent > 100 Then
  WScript.Echo "ERROR: percent to select is not between 1 and 100."
End If

' Open CSV file for list of files selected
Set oLogFile = oFSO.OpenTextFile(sLogFileName, ForWriting, True)

' Load files from source folder into array for processing
Set oFolder = oFSO.GetFolder(sFromFolder)
Set oFiles = oFolder.Files
ReDim oPath(oFiles.Count)
iIndex = -1
For Each oFile in oFiles
   iIndex = iIndex + 1
   oPath(iIndex) = oFile.Path

' Randomly select the percent files we want for audit check
For iCopy = 0 To (oFiles.Count * iPercent / 100) - 1
   sFileToCopy = ""
      iIndex = Int((oFiles.Count - 1) * Rnd)
      If oPath(iIndex) <> "" Then
         sFileToCopy = oPath(iIndex)
         oPath(iIndex) = ""
      End If
   Loop Until sFileToCopy <> ""
   oFSO.CopyFile sFileToCopy, sToFolder
   oLogFile.WriteLine sFileToCopy

' Wrap-up

Open in new window

txdolfanAuthor Commented:
This solution worked perfectly and got me past my issue using VBA to move the files.
Bill PrewCommented:
Great, glad that was useful, thanks.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now