Setting File attributes to Hidden via VBA

[omairhe]

Hey Excel Gurus,

How do I set windows file attributes to hidden state. The cells below shall determine which workbooks need to be hidden and which of the workbooks must remain unhidden.

Please note that there are no formulas in range D2:D13 and Cell F2. However only E2:E13 have formulas in them that determines which workbooks need hiding or unhiding.

To clarify what my requirement is the same as achieving from the properties windows "Hidden" state as below.

Will appreicate a lot.

 

[veyselemre]

Sub changeFileAttributes()

    Dim path$, fileName$, i&
    path = Range("F2").Value & "\"
    For i = 2 To Cells(Rows.Count, 4).End(3).Row
        fileName = Dir(path & Replace(Cells(i, 4).Value, "-", "*") & ".*", vbHidden + vbNormal)

        Do While fileName <> ""
            SetAttr path & fileName, IIf(Cells(i, 5).Value = "unhide", vbNormal, vbHidden)
            fileName = Dir()
        Loop
    Next i

End Sub

 

[omairhe]

>>

 

[omairhe]

Sub changeFileAttributes()

    Dim path$, fileName$, i&
    path = Range("F2").Value & "\"
    For i = 2 To Cells(Rows.Count, 4).End(3).Row
        fileName = Dir(path & Replace(Cells(i, 4).Value, "-", "*") & ".*", vbHidden + vbNormal)

        Do While fileName <> ""
            SetAttr path & fileName, IIf(Cells(i, 5).Value = "unhide", vbNormal, vbHidden)
            fileName = Dir()
        Loop
    Next i

End Sub

Hello, I tried and the code didn't work as expected the first time. then I tested a little more and the problem seems to be the filenames. the code will work on files where the names are 01-2020.xlsm But the files I am working with goes like 01-2020 (omair.enam).xlsm
In such an instance the code doesn:t seem to work nor it gives any errors.
Will appreciate further assistance.

 

[GWteB]

This code is assumed to work. It leaves, as opposed to @veyselemre's code, existing file attributes such as Archive or Read-Only unaffected.

Sub ChangeFileAttr()

    Dim FSO As Object, oFolder As Object, oFile As Object
    Dim rng As Range, c As Range
    Dim sPath As String, sPrefix As String
    Dim vbAttr As VbFileAttribute

    Set FSO = CreateObject("Scripting.FileSystemObject")

    With ActiveSheet
        sPath = .Range("F2").Text
        If FSO.FolderExists(sPath) Then
            Set oFolder = FSO.GetFolder(sPath)
            Set rng = .Range("D2:D" & .Cells(.Rows.Count, "D").End(xlUp).Row)
            For Each c In rng
                sPrefix = Trim(c.Text)
                For Each oFile In oFolder.Files
                If StrComp(Left(oFile.Name, Len(sPrefix)), sPrefix, vbTextCompare) = 0 Then
                    vbAttr = oFile.Attributes
                    If StrComp(c.Offset(0, 1), "hide", vbTextCompare) = 0 Then
                        vbAttr = vbAttr Or vbHidden
                    Else
                        vbAttr = (vbAttr Or vbHidden) Xor vbHidden
                    End If
                    oFile.Attributes = vbAttr
                End If
                Next oFile
            Next c
        End If
    End With
End Sub

 

[omairhe]

for the code that was sent to me on post 2 I had to add one more asterisk * before .* on the following line like this

        fileName = Dir(path & Replace(Cells(i, 4).Value, "-", "*") & "*.*", vbHidden + vbNormal)

I checked and both codes working.
Thanks veyselemre and GWteB

 

[omairhe]

Sub changeFileAttributes()

    Dim path$, fileName$, i&
    path = Range("F2").Value & "\"
    For i = 2 To Cells(Rows.Count, 4).End(3).Row
        fileName = Dir(path & Replace(Cells(i, 4).Value, "-", "*") & ".*", vbHidden + vbNormal)

        Do While fileName <> ""
            SetAttr path & fileName, IIf(Cells(i, 5).Value = "unhide", vbNormal, vbHidden)
            fileName = Dir()
        Loop
    Next i

End Sub

One last favour I need . I need to know how I can make this work only from within an Active Workbook and Sheet named "HSheet" . At the moment I need to be on HSheet for this to work and will not work if I am at any other sheet. Thanks.

 

[omairhe]

Ok I got this by searching the web a little.

Sub ChangeAtt()
    Dim path$, fileName$, i&
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("HSheet")
    path = ws.Range("B17").Value
    For i = 2 To ws.Cells(Rows.Count, 4).End(3).Row
        fileName = Dir(path & (ws.Cells(i, 4).Value) & "*", vbHidden + vbNormal)
        Do While fileName <> ""
            SetAttr path & fileName, IIf(ws.Cells(i, 5).Value = "unhide", vbNormal, vbHidden)
            fileName = Dir()
        Loop
    Next i
End Sub

Needed to set wb and ws properties

 

[veyselemre]

I have very little English. Check out the solution if I don't get it wrong.

Sub ChangeAtt()
    If ActiveWorkbook.Name <> "HSheet.xlsm" Or ActiveSheet.Name <> "HSheet" Then
        MsgBox "ActiveWorkbook.Name <> 'HSheet' Or ActiveSheet.Name <> 'HSheet'", vbCritical
        Exit Sub
    End If
    Dim path$, fileName$, i&
   
    path = Range("B17").Value
    For i = 2 To Cells(Rows.Count, 4).End(3).Row
        fileName = Dir(path & (Cells(i, 4).Value) & "*", vbHidden + vbNormal)
        Do While fileName <> ""
            SetAttr path & fileName, IIf(ws.Cells(i, 5).Value = "unhide", vbNormal, vbHidden)
            fileName = Dir()
        Loop
    Next i
    MsgBox "Ok..."
End Sub