• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

to bring the file name if it is readonly

A

ajaar

Member
HI Friends,

I have below code working well, to bring the excel file name in particular folder, but i wanted only to bring the file name if it is read only, which part of the code i have to change for this. i tried changing File = Dir(Path & "*.xlsx",wbreadonly). still it is not working. any help appreciated.

Code: 
Sub bringfilename()
 
Dim ctr As Integer
ctr = 5
Path = "G:\General\"
File = Dir(Path & "*.xlsx")
Do Until File = ""
ActiveSheet.Cells(ctr, 2).Value = File
ctr = ctr + 1
File = Dir()
Loop
end sub


Regards
Ajaar
 
Hi Marc,

Thanks for your time.
I corrected spelling dis as below, still it is bringing all the file name, i wanted to bring only the file name with read only.

File = Dir(Path & "*.xlsx", vbReadOnly)

Regards
Ajaar
 

You forgot to check the attribute of the file with GetAttr function
like in the Dir VBA help example for vbDirectory …
​
 
Hi Marc,

I dont know this, i tried lot of trial and error, still no result.
Could you please change the code.

Regards
Ajaar
 

Example from VBA inner help of Dir function :​
Code: 
         MyPath = "c:\"
         MyName = Dir(MyPath, vbDirectory)
Do While MyName > ""
      If MyName <> "." And MyName <> ".." Then
        If (GetAttr(MyPath & MyName) And vbDirectory) = vbDirectory Then Debug.Print MyName
      End If
         MyName = Dir
Loop
So difficult to read and to amend to your needs ?‼
Just need GetAttr line …

Faster solution just by reading VBA inner help … :rolleyes:
​
 
Dear Marc,

thanks for your time.

Still not working

I hope i have conveyed my requirment, i wanted to bring the all excel file name with read only from a particular folder.

if it is difficult to read and amend, dont read it, instead provide a code which do this task.. o_O. i have alredy tried all possible to me.

Regards
Ajaar
 
HI,

Yes, now it is brining after modifying a little, below is the code for your reference. thanks you.
Code: 
Sub new1()
Dim ctr As Double
ctr = 5

 MyPath = "G:\General"
 
  myname = Dir(MyPath, vbDirectory)
 Do While myname > ""
  If myname <> "." And myname <> ".." Then
  If (GetAttr(MyPath & myname) And vbReadOnly) = vbReadOnly Then 'Debug.Print MyName
  
  ActiveSheet.Cells(ctr, 24).Value = myname
  ctr = ctr + 1
  End If
  End If
  
  
  myname = Dir
 Loop
 
 End Sub
 

Mistake in first Dir codeline ‼
And as written, you just need the GetAttr line …
Codeline If MyName <> "." … is for directory only !​

Code: 
Sub Demo
                 Const FPath = "D:\Tests4Noobs\"
             F$ = Dir$(FPath & "*.xlsx", 1)
    Do Until F = ""
        If (GetAttr(FPath & F) And 1) = 1 Then Debug.Print F
        F = Dir$
    Loop
End Sub
 
You must log in or register to reply here.
Back
Top