Im trying to come up with a piece of script that will allow me to copy 100 files from one folder and create a new folder based on the first file and last file name and then move those 100 files to that folder. After moving those files, i want it to display the folders in a userform listbox as clickable items. For example, each item in the listbox will be a folder, if i double click on a folders name it will display all the contents of the file (of each of 100 files) in a sheet i've set up.
I haven't been able to test this code yet, all i've done for the past week was research and rewrite the code over and over until i could understand it properly before adding it to the program. So there's bound to be some or more errors along the way.
What i did notice was the "objFile.CopyFile Folderpath & FCount & "_" & LCount" piece of code that doesnt specify which files could be copied specifically. For example, i want it to start at the first file and start coping the first 100 files, when the code is executed again, it will start at file 101 and copy the next 100 files. If there's way to ensure that it wouldnt keep copying the first 100 files, that would be awesome!
Sub Main()
'====CHECK IF THERE'S 100 FILES====
Dim filename, folderpath, path As String
Dim count As Integer
Dim FCount, LCount, FlagCount, IntCount As Integer
Dim objFSO As Object
Dim obj As Object
FCount = 0 ' First File name
LCount = 0 'Last file name
count = 0 'file count
FlagCount = Sheets("Flag Sheet").Range("A2").Value
folderpath = "Work\Big Book\" '==================Location Of The Book
path = folderpath & "*.xls"
filename = Dir(path)
Do While filename <> ""
count = count + 1
filename = Dir(path)
Loop
If count < 100 Then
'====CREATE A FOLDER FOR THE FILES====
If FlagCount <> "" Then '====If there is a flag count, it will create a folder based on the last number it was used
FCount = FlagCount + 1
LCount = FlagCount + 101
MkDir folderpath & FCount & "_" & LCount
Else '=======================else if there isnt one, it will use the first file name to create the folder
FCount = IntCount + 1
LCount = IntCount + 100
MkDir folderpath & FCount & "_" & LCount
End If
'====MOVE 100 FILES TO FOLDER====
For Each objFile In objFSO.GetFolder(path)
If FlagCount <> "" Then '====================if theres a flag count it will move the files starting after the flag count + 101
objFile.CopyFile folderpath & FCount & "_" & LCount
IntCount = FlagCount + 1
If IntCount = FlagCount + 100 Then Exit For
Else '======================================else it will just move the first 100 files
objFile.CopyFile folderpath & FCount & "_" & LCount
IntCount = IntCount + 1
If IntCount = IntCount + 100 Then Exit For
End If
Next
End If
Else
'===Do Nothing===
End If
End Sub
'=====Display Folders In Listbox=====
'====Display Folder Items In Book====
'Call the function
DisplayFoldersInListBox folderpath & FCount & "_" & LCount, Me.Listbox1
Sub Button_Click()
For Each File in Folderpath & FCount & "_" & LCount & "\" & Listbox.value
'[INSERT BIG BOOK CODE]
Next
End Sub
Private Sub DisplayFoldersInListBox(ByVal strRootFolder As String, ByRef lbxDisplay As MSForms.ListBox)
Dim fso As Object
Dim fsoRoot As Object
Dim fsoFolder As Object
'Make sure that root folder contains trailing backslash
If Right$(strRootFolder, 1) <> "\" Then strRootFolder = strRootFolder & "\"
'Get reference to the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
'Get the root folder
Set fsoRoot = fso.GetFolder(strRootFolder)
'Clear the listbox
lbxDisplay.Clear
'Populate the listbox with subfolders of Root
For Each fsoFolder In fsoRoot.SubFolders
lbxDisplay.AddItem fsoFolder.Name
Next fsoFolder
'Clean up
Set fsoRoot = Nothing
Set fso = Nothing
End Sub
This link: Copy only the first file of a folder VBA Seems to be the answer for the coping of the files, but im not entirely sure how to add it to my script. Can anyone help me out?
Back to the basics:
Sub CopyXNumberOfFiles(SourceFolder As String, TargetFolder As String, Optional MaxNumFiles As Long = 100)
Dim fso As Object, objFile As Object
Dim count As Long
Dim Path As String
Set fso = CreateObject("Scripting.FileSystemObject")
If Not Right(SourceFolder, 1) = "\" Then SourceFolder = SourceFolder & "\"
If Not Right(TargetFolder, 1) = "\" Then TargetFolder = TargetFolder & "\"
For Each objFile In fso.GetFolder(SourceFolder).Files
If objFile.Path Like "*.xls?" Then
Path = TargetFolder & objFile.Name
If Len(Dir(Path)) = 0 Then
FileCopy objFile.Path, Path
count = count + 1
If count >= MaxNumFiles Then Exit For
End If
End If
Next
End Sub
CopyXNumberOfFiles "C:\","C:\Data"
This function will copy the files over and return an array of the new file paths.
Function getCopyXNumberOfFiles(SourceFolder As String, TargetFolder As String, Optional MaxNumFiles As Long = 100) As String()
Dim fso As Object, objFile As Object
Dim count As Long, n As Long
Dim Path As String
Dim data() As String, results() As String
ReDim data(1 To 2, 1 To MaxNumFiles)
Set fso = CreateObject("Scripting.FileSystemObject")
If Not Right(SourceFolder, 1) = "\" Then SourceFolder = SourceFolder & "\"
If Not Right(TargetFolder, 1) = "\" Then TargetFolder = TargetFolder & "\"
For Each objFile In fso.GetFolder(SourceFolder).Files
If objFile.Path Like "*.xls?" Then
Path = TargetFolder & objFile.Name
If Len(Dir(Path)) = 0 Then
FileCopy objFile.Path, Path
count = count + 1
data(1, count) = objFile.Path
data(2, count) = Path
If count >= MaxNumFiles Then Exit For
End If
End If
Next
ReDim Preserve results(1 To count, 1 To 2)
For n = 1 To count
results(n, 1) = data(1, n)
results(n, 2) = data(2, n)
Next
getCopyXNumberOfFiles = results
End Function
Column 1 has the original paths and column 2 has the new paths.
Dim Files() as String, firstFilePath as String, lastFilePath as String Files = getCopyXNumberOfFiles("C:\", "C:\New Folder\", 100)
Original Paths
firstFilePath = Files(1, 1) lastFilePath = Files(Ubound(Files), 1)
New Paths
firstFilePath = Files(1, 2) lastFilePath = Files(Ubound(Files), 2)