Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #2  
Old 03-31-2016, 09:52 PM
gmayor's Avatar
gmayor gmayor is offline Compare filenames listed in Excel to external folder. Windows 10 Compare filenames listed in Excel to external folder. Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,144
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

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
Reply With Quote
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare filenames listed in Excel to external folder. 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 filenames listed in Excel to external folder. Compare data in different columns in an Excel sheet kgfendi Excel 5 05-16-2009 05:42 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:09 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft