That Missing file!!

[Bootney]

I have a finance folder inside over 3000 multiple folders increment i.e.. 10020-Inv, 10021-Inv, etc..
Each folder must have an invoice with folder reference 10021-inv.doc. 10021-inv.doc

so..

Folder name..... Invoice inside
10020-Inv ..... 10020-inv.doc
10021-Inv ..... 10021-inv.doc
10022-Inv
10023-Inv ..... 10023-inv.doc

Folder 10022-Inv has NO Invoice inside folder
I need a routine to search inside the Finance folder and subfolders
And list which folders are missing invoice docs
With folder name, and hyperlink folder location list on a spreadsheet.

Any advice...

[mdmackillop]

What Office version are you using?

[GTO]

:wavey: Hi Malcom


http://www.mrexcel.com/forum/showthread.php?t=457943

Bootney, you may wish to read Here (http://www.excelguru.ca/node/7).

[Bootney]

What Office version are you using?
Am Using Office 2003 - Excel 2003

[lucas]

Bootney, did you follow the link that GTO provided?

[mdmackillop]

You cannot hyperlink to a Folder

Sub Test()
Dim arr() As String
ReDim arr(4000)
mypath = "c:\" '<===Change to suit
myname = Dir(mypath, vbDirectory)
Do While myname <> ""
If myname <> "." And myname <> ".." Then
If Left(myname, 3) = "100" Then
arr(i) = mypath & myname
i = i + 1
End If
End If
myname = Dir
Loop
ReDim Preserve arr(i - 1)
For j = 0 To i - 1
fname = Dir(arr(j) & "\*.doc")
If fname = "" Then
k = k + 1
Cells(k, 1) = arr(j)
Else 'for debug check
m = m + 1
Cells(m, 5) = arr(j) & "\" & fname
End If
Next
End Sub

[Bootney]

You cannot hyperlink to a Folder

Sub Test()
Dim arr() As String
ReDim arr(4000)
mypath = "c:\" '<===Change to suit
myname = Dir(mypath, vbDirectory)
Do While myname <> ""
If myname <> "." And myname <> ".." Then
If Left(myname, 3) = "100" Then
arr(i) = mypath & myname
i = i + 1
End If
End If
myname = Dir
Loop
ReDim Preserve arr(i - 1)
For j = 0 To i - 1
fname = Dir(arr(j) & "\*.doc")
If fname = "" Then
k = k + 1
Cells(k, 1) = arr(j)
Else 'for debug check
m = m + 1
Cells(m, 5) = arr(j) & "\" & fname
End If
Next
End Sub


Could you tell me How this code works?

[mdmackillop]

It checks for files starting with "100" and lists them in an array
It checks each in the array to see if it contains a file; if not, it puts the name on the sheet in column 1 otherwise it puts the folder and filename in column 5