Can you determine if hyperlink points to empty folder?

[Denise]

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?

 

[Dave Peterson]

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

 

[Héctor Miguel]

hi, Denise !

as an alternative to excelent Dave's proposal
following is an example to check if any file exist for a given folder (or it's empty)
macros are not required and you could modify acording other needs

assuming your hyperlinks looks like a "written path" (say in B2) (e.g.)
C:\Documents and Settings\<user>\My documents\Personal files\Family photos\

select same row one column to right (C2) <= it is important, path should be same row one column-left
and define a name using the xl-4 macro function =files(...)

- (menu) insert / name / define...
- name (i.e.) hasFiles
- formula: =if(isna(files(!b2&"*.*")),"","X")

(now) use "the name" in C2 (first test-cell) or copy-down, or in any (C)-cell for a (B)-hyperlink
and you will get an "X" if B-column (hyperlink-path) has file(s)

notes:
- if your hyperlink-path has NO the last path-separator ( \ ), you *must* provide it in the named formula
=if(isna(files(!b2&"\*.*")),"","X")

- if you need to check for specific file EXTension, modify the same in the named formula:
=if(isna(files(!b2&"*.jpg")),"","X")

- if you modify the path(hyperlink) in B-column AFTER used/pasted/... your defined-name (in C-column)
you will need to make "volatile" the defined name formula... (i.e.)
=if(isna(files(!b2&"*.jpg")&rept("",0*now())),"","X")

hth,
hector.

__ original post __

 

[Denise]

Héctor,

Thanks for your quick response. However, i am having a problem. I'm
wondering if it is because of the way the hyperlink was set up. To minimize
the amount of work in the initial creation of the spreadsheet we used the
=Hyperlink function using an existing column to build the file name. For
example
=HYPERLINK("c:/Photos/"&D6,+D6&" Pictures")
Would this prevent your solution from working for me?

 

[Héctor Miguel]

hi, Denise !

you only need to take care on two things:

1) define "the name" where in the cell (active) you want the "X" indicator
which row points to the variable "path" cell (if path in D6, X-indicator in F6 ?)

2) "build the path" as needed by =files(...) macro-funcion's argument "asks for lookin in"
and don't forget the "last" path separator (and the filetype EXTension -if any- or "*.*")

i.e. define "the name" while in F6 (or any column "pointing" to first path-variable en D6 row)
formula: =if(isna(files("c:/photos/"&!d6&"*.*")),"","X")

(again): don't forget the "last" path separator (and the filetype EXTension -if any- or "*.*")

if any doubts (or further information)... would you please comment ?
regards,
hector.

__ this post __

... i am having a problem. I'm wondering if it is because of the way the hyperlink was set up.
To minimize the amount of work in the initial creation of the spreadsheet we used the =Hyperlink function
using an existing column to build the file name. For example=HYPERLINK("c:/Photos/"&D6,+D6&" Pictures")
Would this prevent your solution from working for me?

__ second post __