![]() |
#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 |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
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 |
![]() |
kgfendi | Excel | 5 | 05-16-2009 05:42 PM |