Why Dir(my_folder, vbHidden) fails to work as promissed?

[grautu]

The VBA code

Dim item as String
item = Dir(my_folder, vbNormal)
Do While item <> ""
   MsgBox item
   item = Dir
Loop

lists in turn all the "normal" files from within my_folder, where "normal" means "without attributes". Now replacing vbNormal by vbHidden, the above code should list not only the normal, but also the hidden files as well. However, the two options only list normal files and the same result occurs if using vbReadOnly or vbSystem options instead of vbNormal: only normal files are listed although all my system settings do allow complete visibility of files.
Could anybody please explain me how to obtain a completely functional Dir(my_folder, *) function as promissed by VBA?
Thanks in advance!
------------------------------
P.S.1 The shell command Dir does work as expected. For instance, Dir /A:H does list all the hidden files, etc.
P.S.2 I'm working with Excel 2007, under Win XP SP2

 

[BrianB]

I have a feeling this is a bug.
I thought that I could solve the problem by using GetAttr() function. The reason being that many files combine several attributes so individual ones have to be separated with something like :-

h = GetAttr("C:\autoexec.bat") And vbHidden

On trying this on the few files in my C:\ drive - which show a variety of attributes combinations in Explorer, the function returned the same overall value for each one :- 8224. This is odd, because GetAttr() , supposedly, returns the total of the attribute numbers. A Read Only (1) + Hidden(2) file would return 3. So the maximum total possible would be 1+2+4+8+16+64 = 95.

So we have a mystery here.

To get something working I adapted some of my existing code. Interestingly, the attributes for autoexec.bat and config.sys still do not agree with Explorer.

'=======================================================
'- SHOW FILE PROPERTIES & ATTRIBUTES OF A FOLDER
'- Brian Baulsom July 2007
'=======================================================
Sub ShowAttr()
    Dim FSO As Object
    Dim MyFolder As String
    Dim f, fc
    Dim MyAttr  As Integer
    Dim MyAttrString As String
    Dim MySheet As Worksheet
    Dim ToRow As Long
    '-------------------------------------------------------------------------------
    '- FOLDER NAME
    MyFolder = "C:\"
    '-------------------------------------------------------------------------------
    Set FSO = CreateObject("Scripting.FileSystemObject")
    '- set up worksheet
    Set MySheet = ActiveSheet
    ToRow = 2
    MySheet.Columns("A:F").ClearContents
    MySheet.Range("A1:F1").Value = Array("FOLDER", "FILE NAME", "CREATED", "SIZE", "ATTR", "ATTR NAMES")
    ToRow = 2
    '--------------------------------------------------------------------------------
    Set f = FSO.GetFolder(MyFolder)
    Set fc = f.Files
    '--------------------------------------------------------------------------------
    '- CHECK FILE COUNT
    If fc.Count = 0 Then
        ' no files
         Exit Sub
    Else
        '- LOOP FILES
        For Each f1 In fc
            Set Spec = FSO.GetFile(f1)                          ' individual file info
            MySheet.Cells(ToRow, 1).Value = MyFolder
            MySheet.Cells(ToRow, 2).Value = f1.Name
            MySheet.Cells(ToRow, 3).Value = Spec.datecreated
            MySheet.Cells(ToRow, 4).Value = Spec.Size           'bytes
            '----------------------------------------------------------------------------
            MyAttr = Spec.Attributes                            'integer
            MySheet.Cells(ToRow, 5).Value = MyAttr
            '----------------------------------------------------------------------------
             MyAttrString = ""
            If MyAttr And vbReadOnly Then MyAttrString = MyAttrString & "ReadOnly" '  1
            If MyAttr And vbHidden Then MyAttrString = MyAttrString & "-Hidden"    '  2
            If MyAttr And vbSystem Then MyAttrString = MyAttrString & "-System"    '  4
            If MyAttr And vbArchive Then MyAttrString = MyAttrString & "-Archive"  ' 32
            If MyAttrString = "" Then MyAttrString = "Normal"                      '  0
            MySheet.Cells(ToRow, 6).Value = MyAttrString
            '------------------------------------------------------------------------------
            ToRow = ToRow + 1
        Next
    End If
    MsgBox ("Done")
End Sub
    '================= end of proc ==================================================

 

[grautu]

Thank a lot BrianB!
To be frank, after one day of no answering my question, I was thinking nobody will answer it at all .
As for the bug, though I'm not sure, I would consider it as being almost intentionally introduced in Office 2007, for "security reasons". Please notice, in this respect, another bug, in precisely the same matter of searching files by VBA macros; it is a bug which definitely was introduced by Microsoft in Office 2007. Concretely, Office 2007 ceases to support the Application.FileSearch device, though the Office 2007 VBA Help is still tutoring the user about how to use it. Maybe (but I'm not sure about that) Microsoft intends to prevent VBA macros from detecting files with special attributes (the Dir function cannot detect them while Application.FileSearch simply ceased to work at all).
Of course, my considerations are false in case the Dir function behaves identically under Office 2003 as it does under Office 2007. Concretely, I can say that Application.FileSearch was perfectly supported by Office 2003 (and it fails to work under Office 2007) but I do not have at hand a similar comparison for the Dir function.
All in all, it seems that VBA macros (under Office 2007) cannot inspect working directories in order for the user to make completely sure about their contents.
Thanks again for your answer and please do excuse my poor english!

 

[BrianB]

Your message was quite interesting I do not know about Office versions after 2000 (it does everything I need). As new versions of Microsoft applications are introduced there seem to be ever more restrictions placed on us (such as the need to pay money to enable signatures on our work) - one of the reasons I have not upgraded.

A couple of points :-

Windows Scripting is not directly connected with Microsoft Office, it is a separate 'application' supplied as part of the Windows operating system and includes Windows Scipting host (WSH) to run the scripts. The fact that VBA and Visual Basic can access it is a bonus. It can be used on its own by writing special files in Notepad or other text editor with a .vbs suffix. There is lots of info about it on the net. I have never used it on its own (not had to). Might be your way to go. Be interested to know how you get on.

after one day of no answering my question, I was thinking nobody will answer it at all .

Need to be a bit patient here. You would be lucky to get an answer as quickly as this - especially for someting as labour intensive. We are only part-timers, having other things to do like earning a living. Personally, I leave time-consuming questions a day or two to see if someone else answers.

As for the bug ..I would consider it as being almost intentionally introduced in Office 2007

As noted on bottom of my message, I use Office 2000.