Finding read-only & pwd protection attibutes of Word & Excel files

[Guest]

Hi , I'm using the application.filesearch method in an Excel macro to
identify a list of Word and Excel files. I want to check the read-only and
password protection attributes of these word & Excel files and return a flag
to a populated list in Excel.

What object allows me access these attributes? Cheers.

 

[Guest]

Application.Filesearch will not cut it for what you want to do. You really
want a file system object. Here is some code to demonstrate them. Note that
in the VB editor you need to reference "Microsoft Scripting Runtime" for this
to work. (Tools -> References -> Microsoft Scripting Runtime.

Sub ListFiles()
'Must reference "Microsoft Scripting Runtime" library

Dim fso As New FileSystemObject
Dim oCurrentFile As File
Dim oCurrentFolder As Folder
Dim wks As Worksheet
Dim rng As Range

On Error Resume Next
Set wks = Sheets("Sheet1")
Set rng = wks.Range("A2")

Set oCurrentFolder = fso.GetFolder("C:\FPA")

For Each oCurrentFile In oCurrentFolder.Files
rng.Value = oCurrentFile.Name
rng.Offset(0, 1).Value = oCurrentFile.ShortName
rng.Offset(0, 2).Value = oCurrentFile.Path
rng.Offset(0, 3).Value = oCurrentFile.DateCreated
rng.Offset(0, 4).Value = oCurrentFile.DateLastAccessed
rng.Offset(0, 5).Value = oCurrentFile.DateLastModified
rng.Offset(0, 6).Value = oCurrentFile.Size
rng.Offset(0, 7).Value = oCurrentFile.Type
rng.Offset(0, 8).Value = oCurrentFile.Attributes
Set rng = rng.Offset(1, 0)
Next oCurrentFile
End Sub

 

[reachthepalace]

Searching this forum, I came up with this modified method of searchin
for files and creating a list. To begin with I was using the Fil
Search option, but I couldn't work it to list all the file attributes.
Sub Filelist_wo_Attributes()
'
' Filelist Macro
With Application.FileSearch
.LookIn = "c:\MyDir" '<== Change and set this to your directory
.FileType = msoFileTypeAllFiles
.SearchSubFolders = True
.Execute
For I = 1 To .FoundFiles.Count
Cells(I, 1) = .FoundFiles(I)
Next I
End With
End Sub

This modified code does list all the file attributes, but how do I wor
it to search sub-folders as well.

Sub ListFiles()
'Must reference "Microsoft Scripting Runtime" library (Tools -
References -> Microsoft Scripting Runtime)
' Code taken from this forum posted by Jim Thomlinson on 09/26/05
Dim fso As New FileSystemObject
Dim oCurrentFile As file
Dim oCurrentFolder As Folder
Dim wks As Worksheet
Dim rng As Range

On Error Resume Next
Set wks = Sheets("Sheet2")
Set rng = wks.Range("A2")

Set oCurrentFolder = fso.GetFolder("O:\OverFlow\OCP\0859\04\0304")
'<== Set your desired folder path here

For Each oCurrentFile In oCurrentFolder.Files
rng.Value = oCurrentFile.Name
rng.Offset(0, 1).Value = oCurrentFile.ShortName
rng.Offset(0, 2).Value = oCurrentFile.Path
rng.Offset(0, 3).Value = oCurrentFile.DateCreated
rng.Offset(0, 4).Value = oCurrentFile.DateLastAccessed
rng.Offset(0, 5).Value = oCurrentFile.DateLastModified
rng.Offset(0, 6).Value = oCurrentFile.Size
rng.Offset(0, 7).Value = oCurrentFile.Type
rng.Offset(0, 8).Value = oCurrentFile.Attributes
Set rng = rng.Offset(1, 0)
Next oCurrentFile
End Sub
You can consider me a novice to vb programming.

Thanks
Dines

 

[Tom Ogilvy]

http://support.microsoft.com/kb/185601/EN-US/
HOW TO: Recursively Search Directories by Using FileSystemObject

--
Regards,
Tom Ogilvy


"reachthepalace"