Now, let us assume we have an Excel file with a list of books that we need to deliver to one of our clients. The list is in tabular format (see the above image, 2nd column). We need to search these books in a folder, which already has a collection of many books in it, and check if it is available or not.
It is a very practical scenario and I have come across many such requirements in the past. If the books are in our inventory (in the folder) then the File Status column will show the status “On Hand” and the files will be copied or moved from the source folder to the destination folder.
Coping or moving files across different folders or directories, play a slightly different role. With many other methods, the File System Object has provided us with 2 distinct methods such as MoveFile and CopyFile.
Syntax: object.MoveFilesource, destination
Set objFSO = CreateObject("scripting.filesystemobject") objFSO.MoveFile
The objFSO.MoveFile will move the files permanently from the source folder to the destination folder. Its function is similar to cutting and pasting an object.
Syntax: object.CopyFilesource, destination, overwrite
Set objFSO = CreateObject("scripting.filesystemobject") objFSO.CopyFile
The CopyFile method will copy the files from the source folder to the destination folder. The Original copy of the file remains in the source folder. Other than source and destination, the 3rd parameter is a Boolean value overwrite. If set as True the files in the destination folder will be overwritten and if set false, the file in the destination folder will remain unchanged.
You can copy or move any type of file in a folder. In the demo example here, I’ll search for .jpg files only, ignoring other types of files.
Open an excel file and create a table and add few rows in it. Once the table is created, press Alt+F11 which will open a module in the visual basic IDE. Copy and paste the code below into the module and save the file as macro enabled file. Run the macro by pressing F5 and see the result both in the excel file itself and destination folder.
Option Explicit Sub CopyFiles() Dim iRow As Integer ' ROW COUNTER. Dim sSourcePath As String Dim sDestinationPath As String Dim sFileType As String Dim bContinue As Boolean bContinue = True iRow = 2 ' THE SOURCE AND DESTINATION FOLDER WITH PATH. sSourcePath = "C:\books\" sDestinationPath = "C:\booksforclient\" sFileType = ".jpg" ' TRY WITH OTHER FILE TYPES LIKE ".pdf". ' LOOP THROUGH COLUMN "B" TO PICK THE FILES. While bContinue If Len(Range("B" & CStr(iRow)).Value) = 0 Then ' DO NOTHING IF THE COLUMN IS BLANK. MsgBox "Process executed" ' DONE. bContinue = False Else ' CHECK IF FILES EXISTS. If Len(Dir(sSourcePath & Range("B" & CStr(iRow)).Value & sFileType)) = 0 Then Range("C" & CStr(iRow)).Value = "Does Not Exists" Range("C" & CStr(iRow)).Font.Bold = True Else Range("C" & CStr(iRow)).Value = "On Hand" Range("C" & CStr(iRow)).Font.Bold = False If Trim(sDestinationPath) <> "" Then Dim objFSO Set objFSO = CreateObject("scripting.filesystemobject") ' CHECK IF DESTINATION FOLDER EXISTS. If objFSO.FolderExists(sDestinationPath) = False Then MsgBox sDestinationPath & " Does Not Exists" Exit Sub End If '***** ' HERE I HAVE INCLUDED TWO DIFFERENT METHODS. ' I HAVE COMMENTED THE SECOND METHOD. TO THE SEE THE RESULT OF THE ' SECOND METHOD, UNCOMMENT IT AND COMMENT THE FIRST METHOD. ' METHOD 1) - USING "CopyFile" METHOD TO COPY THE FILES. objFSO.CopyFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _ sFileType, Destination:=sDestinationPath ' METHOD 2) - USING "MoveFile" METHOD TO PERMANENTLY MOVE THE FILES. 'objFSO.MoveFile Source:=sSourcePath & Range("B" & CStr(iRow)).Value & _ sFileType, Destination:=sDestinationPath '***** End If End If End If iRow = iRow + 1 ' INCREMENT ROW COUNTER. Wend End Sub
These are useful methods and the File System Object comes with a package of many useful methods. However, before using these functions, you must make sure that the folders have necessary permissions. Since many of these methods will have no result if the folder attributes are set as read-only. You might also need permissions from the server on which you are performing these functions.