Could you use a macro instead of a hyperlink?

I put a bunch of folder names in column A (A2:Axx) -- headers in row 1.

And I could run this macro:

Option Explicit
Sub testme()
Dim FSO As Object
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set FSO = CreateObject("Scripting.FileSystemObject")

For Each myCell In myRng.Cells
If FSO.folderexists(myCell.Value) = False Then
myCell.Offset(0, 1).Value = "Invalid folder name"
Else
myCell.Offset(0, 1).Value = FSO.getfolder(myCell.Value).Files.Count
myCell.Offset(0, 2).Value _
= FSO.getfolder(myCell.Value).subFolders.Count
End If
Next myCell
End Sub

It actually puts a quantity of files or folders in the adjacent cells.

If you really wanted an X, you could use this:

Option Explicit
Sub testme()
Dim FSO As Object
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim FileCount As Long
Dim FolderCount As Long
Dim FileStr As String
Dim FolderStr As String

Set wks = ActiveSheet
With wks
Set myRng = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

Set FSO = CreateObject("Scripting.FileSystemObject")

For Each myCell In myRng.Cells
If FSO.folderexists(myCell.Value) = False Then
myCell.Offset(0, 1).Value = "Invalid folder name"
Else
FileCount = FSO.getfolder(myCell.Value).Files.Count
FolderCount = FSO.getfolder(myCell.Value).subFolders.Count

If FileCount = 0 Then
FileStr = ""
Else
FileStr = "X"
End If

If FolderCount = 0 Then
FolderStr = ""
Else
FolderStr = "X"
End If

myCell.Offset(0, 1).Value = FileStr
myCell.Offset(0, 2).Value = FolderStr
End If
Next myCell
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Denise wrote:

Our little cabana club uses Excel for our a photo id system. Each member
household has a folder that contains photos for their family members. One
column of the spreadsheet contains a hyperlink to each folder. i am trying
to do something that requires me to know which folders are empty, and which
folders contain at least one picture. Currently i am keeping track of this
manually (putting an X the column next to the hyperlink if the folder is not
empty.) This is time consuming and only as accurate as the last time i
update this column. Also, it is possible i might put an X where it doesn't
belong or omit one that does.

So i was wondering if there was a way to automate this. Is there any
function or macro, etc. that would automatically determine if the hyperlink
points to an empty folder or not?
--
Thanks for the help - Denise

--

Dave Peterson