File count excluding those which are hidden

[K]

Hi all, I am using excel 2007 and I got an excel file call "Test File"
in folder "My Document". When I open "Test File" I can see another
file appear in same folder with same name the only difference is that
it got "~$" in the beginning of its name like "~$Test File". I know
these are hidden files and only appear when you open file and I also
know that if you dont want to see them then unselect "Show hidden file
and folders" option under "Tools+FolderOption+View". These files
hidden attribute is alwasy selected by default. Ok now i come to my
problem. I got macro (see below) which count files in folder "My
Document".

Private Sub CommandButton1_Click()
Dim foldername As String
Dim FSO As Object
Dim fldr As Object
Dim file As Object
Dim cnt As Long
foldername = "C:\My Document"
Set FSO = CreateObject("Scripting.FilesystemObject")
Set fldr = FSO.GetFolder(foldername)
For Each file In fldr.Files
If file.Type Like "*Microsoft Office Excel*" Then
cnt = cnt + 1
End If
Next file
Set file = Nothing
Set fldr = Nothing
Set FSO = Nothing
Range("A1").Value = cnt
End Sub

Above macro count the files in folder "My Document" and put the result
in cell "A1". The problem is that if file in "My Document" folder is
open then macro brings the result "2" in cell "A1" as it counts the
hidden file as well. But when file is not open then result comes
"1". I want some adjustment in above macro that it should only count
those files of which attribute in not hidden. Some thing like (see
below).

I tried everything in my knowledge but no success so far. Please can
any friend solve my problem.

 

[Per Jessen]

Hi

You have to look at the Attributes property, where 0 represent a normal
file, 2 is a hidden file etc.

Try this (not tested):

If File.Type LIke "*Microsoft Office Excel*" And file.Attributes=0 then

Hopes this helps
....
Per

 

[Peter T]

if (file.Attributes And vbHidden) = 0 then
'it's not hidden

IOW, the file's attributes does not contain the 2/vbHidden bit

Regards,
Peter T

 

[K]

Thanks lot Peter and Per Jessen. Thats brilliant. Cant believe it was
that simple