Dave
Fri Mar 14 21:40:02 CDT 2008
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