1

I have an Excel file with multiple columns, one of the column has a path to an image in a remote folder (or local, doesn't matter).

\\xxx.xxx.xxx.xxx\folder\image.jpg

I want to check if all the URLs or PATHs to the files are correct.

If one is wrong or doesn't point to a real file (or the address is bad) then highlight it in red (or just signal it somehow).

Hope this makes sense. I know I can do this with Macros or VBA but I don't know the syntax.

Thanks!

3

You can use an UDF (User Defined Function) to check whether a file path is valid or not.

In Excel, hit ALT+F11 to turn on the Visual Basic Editor (VBE).

Right-click anywhere in the VBA Project Explorer » Insert » Module.

Copy and paste the below code

Function FileExist(path As String) As Boolean
    If Dir(path) <> vbNullString Then FileExist = True
End Function

Now, go back to your spreadsheet view. Go into any cell and type:

=FileExist(A1)

where A1 is cell reference that holds the file path

for example:

enter image description here

Additionally, you can use conditional formatting or VBA to highlight cell's based on value.

  • sneaky trick to remove reliance on the default of the boolean variable: FileExist=Len(Dir(path))>0 – SeanC Sep 25 '13 at 13:43
  • @SeanCheshire yea but why would I? – user222864 Sep 25 '13 at 13:45
  • This is great! Wow, I didn't think it was that easy. But it is a little bit slow, is it possible to make it faster, I guess the function "runs" continually searching for the path, and because is a Remote server, it consumes a lot of memory. – villancikos Sep 26 '13 at 15:27
  • anything that has to map network drive will obviously slow down the execution. I honestly cant think of a better (easy) solution, maybe someone else could. Alternative, to this would be to map entire network drive to a txt file or array and work in memory to search for the path using a different approach. this would have been much more complicated and advanced. – user222864 Sep 26 '13 at 15:30
  • @mehow well yes of course searching on a remote drive takes a lot, but I am thinking about running the formula once. Not like every time. Anyway, you resolved my question so you win :P – villancikos Nov 21 '13 at 23:13
0

Figured this out. I'm sure other people are going to read this as I did today. You'll need two modules (one to extract hyperlinks and one to test file path directory)

Module1 (for hyperlinks)

    Function HLink(rng As Range) As String
    'extract URL from hyperlink
     'posted by C.F. Stotch! - shoutout to Richard K!
      If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
    End Function

Module2 (for directory test)

     Function FileOrDirExists(PathName As String) As Boolean 'used to test filepaths of commmand button   links to see if they work - change their color if not working
  'Macro Purpose: Function returns TRUE if the specified file
   Dim iTemp As Integer

 'Ignore errors to allow for error evaluation
On Error Resume Next
iTemp = GetAttr(PathName)

 'Check if error exists and set response appropriately
Select Case Err.Number
Case Is = 0
    FileOrDirExists = True
Case Else
    FileOrDirExists = False
End Select

 'Resume error checking
   On Error GoTo 0
    End Function

''''' AND BELOW IS WHAT YOU WOULD INSERT INTO YOUR SHEET AND ACTIVATE IT HOW YOU WISH, via command button or how you wish. I have it auto run when worksheet is activated :) Cheers!

     Private Sub TestFilesExist()
   Dim xCheck As Integer
  'starting in the 3rd row....
 xCheck = 3
   On Error GoTo 0
    'Debug.Print Range("A" & xCheck).Value
    While xCheck < 36

       'xPather - checks if Z1 is a good path and then either highlights the actual cell in A column red if bad, or no fill if good.
      Dim sPath As String
      Dim XPather As String

'need a cell to put the hyperlink addresses into during the loop check as was not able to find the hyperlink address straight out of the cell containing the hyperlink. Extraction if you will. :)

ThisWorkbook.Sheets(1).Range("Z1").Value = "=HLink(A" & xCheck & ")"
XPather = ThisWorkbook.Sheets(1).Range("Z1").Value

Debug.Print XPather

 'Tests if directory or file exists
If FileOrDirExists(XPather) = False Then
    Range("A" & xCheck).Interior.ColorIndex = 3
Else
    Range("A" & xCheck).Interior.ColorIndex = xlNone
End If

    xCheck = xCheck + 1
    Wend

    End Sub

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.