#1
|
|||
|
|||
Compare filenames listed in Excel to external folder.
I’m hoping someone can help with a solution.
In column A I have a list of filenames in the form 12345.JPG. I’d like to be able to run a macro and enter a folder name in a message box to check if each of the files named in column A exists in the folder entered in the message box. It would then report ‘present’ or ‘not present’ against each item. For example A 123.JPG 124.JPG 125.JPG 195.JPG 203.JPG 12356.JPG Would be compared to the contents of the folder named in the message box , and might report back A B 123.JPG Present 124. JPG Present 125.JPG Present 195. JPG Not Present 203. JPG Not Present 12356.JPG Present Change of colours would help for ‘Present’ or ‘Not Present’ maybe. Grateful for any help. |
#2
|
||||
|
||||
Maybe something like
Code:
Option Explicit Sub CheckFiles() Dim xlSheet As Worksheet Dim lngLast As Long Dim lngCount As Long Dim strPath As String strPath = BrowseForFolder("Select folder to process") Set xlSheet = ActiveSheet With xlSheet lngLast = .Cells(.Rows.Count, "A").End(xlUp).Row For lngCount = 2 To lngLast '=1 if no header row) If FileExists(strPath & .Cells(lngCount, 1)) Then Range("B" & lngCount) = "Present" Range("B" & lngCount).Font.Color = RGB(0, 255, 0) 'Green Else Range("B" & lngCount) = "Not Present" Range("B" & lngCount).Font.Color = RGB(255, 0, 0) 'Red End If Next End With lbl_Exit: Exit Sub End Sub Private Function FileExists(strFullName As String) As Boolean 'Graham Mayor 'strFullName is the name with path of the file to check Dim fso As Object Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(strFullName) Then FileExists = True Else FileExists = False End If lbl_Exit: Set fso = Nothing Exit Function End Function Private Function BrowseForFolder(Optional strTitle As String) As String 'Graham Mayor 'strTitle is the title of the dialog box Dim fDialog As FileDialog On Error GoTo err_Handler Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) With fDialog .Title = strTitle .AllowMultiSelect = False .InitialView = msoFileDialogViewList If .Show <> -1 Then GoTo err_Handler: BrowseForFolder = fDialog.SelectedItems.Item(1) & Chr(92) End With lbl_Exit: Exit Function err_Handler: BrowseForFolder = vbNullString Resume lbl_Exit End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#3
|
|||
|
|||
Hi
OK thanks very much indeed for this. It works fine. I'm impressed! Grateful for your time and expertise. Best Wishes , |
#4
|
|||
|
|||
Hi
Just out of interest could the code you made be modified to check if there are files present in the external folder which are not represented in the list contained in list showing in excel? I assume that the names of these files would need to be copied somehow back into the worksheet and listed there. Best Wishes , |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Excel data linked to external source | Doug Needham | Excel Programming | 1 | 09-15-2015 05:14 AM |
outlook 2010 - external sent mails not appearing in SENT items folder | Leneb | Outlook | 0 | 06-29-2015 05:34 AM |
External/Embedded links to excel | lucas | Word | 3 | 03-30-2012 01:47 PM |
Impossible task: sort email to external folder | billzinn | Outlook | 1 | 07-26-2011 06:25 PM |
Compare data in different columns in an Excel sheet | kgfendi | Excel | 5 | 05-16-2009 05:42 PM |