BuiltinDocumentProperties problem

[nieironsio]

Hello

I am looking for some help using the BuiltinDocumentProperties object. I am trying to adapt it into a directory index macro. What the macro currently does is use FoundFiles to basically create an index of whatever path you want it to. I then want to offset and list various info on the foundfiles here is an exmaple of my code and where its going wrong - the bottom line using BuiltinDocumentProperties. Thanks for any help in advance...

FileNameOnly = Dir(.FoundFiles(i))

ActiveCell.Formula = FileNameOnly

ActiveCell.Offset(0, 2).Formula = FileDateTime(.FoundFiles(i))

ActiveCell.Offset(0, 1).Value = FileLen(.FoundFiles(i)) / 1000

ActiveCell.Offset(0, 7).Value = .FoundFiles(i)

ActiveCell.Offset(0, 4).Value = foundfiles.BuiltinDocumentProperties("Last Author").Value

 

[WalkerEvans]

nieironsio,

From the Excel VBA Help file:

Container applications aren’t required to define values for every built-in document property. If Microsoft Excel doesn’t define a value for one of the built-in document properties, reading the Value property for that document property causes an error.

You need to run a small test loop to determine whether or not "Last Author" is being capture/defined. I hope this helps.



----------------------------------------------------------------------------------
[small]"Did you hear about the guy who refused to pay his exorcist?[/small]
He was re-Possessed."

 

[PHV]

Have a look here:

http://www.microsoft.com/technet/scriptcenter/resources/tales/sg0305.mspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nieironsio]

Hi guys

thanks for the responses guys, i've not managed to get further on either track...Excuse my lack of skills but i couldn't understand quite how to work it and if it could be incorporated in a macro in VBA. And to Walker Evans, thanks, i've tried testing on just a path with xl files in the folder and still no luck

any further help would be great thanks

Nie

 

[MakeItSo]

Hi Nie,

your problem lies with this call:

ActiveCell.Offset(0, 4).Value = [b]foundfiles.[/b]BuiltinDocumentProperties("Last Author").Value

I can see that you are obviously using this line within a
With Application.FileSearch ... End With
block.
Yet you a) reference foundfiles instead of .FoundFiles(see the dot?) and
b) try to read properties not from a file, but simply from a filename. The value of ".FoundFiles(i)" is but a string, not a file.
You can access the files properties by
a) opening it first
b) opening a channel to it and reading its properties only.

The DSOFile does the latter and should be your first choice.

Example for your case:

a) download and install dsofile
b) adjust your code thus:

Dim objFile
Set objFile=CreateObject("DSOFile.OLEDocumentProperties")

...
With Application.FileSearch
   FileNameOnly = Dir(.FoundFiles(i))
   ActiveCell.Formula = FileNameOnly
   ActiveCell.Offset(0, 2).Formula = FileDateTime(.FoundFiles(i))[/red]
   ActiveCell.Offset(0, 1).Value = FileLen(.FoundFiles(i)) / 1000
   ActiveCell.Offset(0, 7).Value = .FoundFiles(i)
   
   [b]objFile.Open .FoundFiles(i)
   ActiveCell.Offset(0, 4).Value = objFile.SummaryProperties.LastSavedBy[/b]

...

Does this help you?
;-)

Cheers,
Andy

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[nieironsio]

Thanks Make It So

I will try that out, i fear though that this macro would be incredibly slow if the drive / path / folder has couple hundred / thousand files in it. Do you know a lateral way round this problem by any chance

Many thanks

 

[nieironsio]

MakeitSo

Really appreciate your time, it seems i am lacking the skills again to make your answer work, i really am a blagger, i just copy paste and adapt codes from the internet with varying success. Would you mind if i post the full code so you can see why i'm failing to incorportae your code?

Thanks

 

[MakeItSo]

I think it would indeed be a lot easier on all of us to do so.
(Unless your code is 5 pages long that is...)


[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[nieironsio]

Thanks very much - here goes

Dim PathName As String
Dim question
Dim filetoopen
Dim lastrow
Dim fs
Dim subfolder
Dim FileNameOnly

Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

question = MsgBox("Do you wish to list files", vbYesNo)

If question = vbYes Then

Dim FileName As String
Dim i As Integer
Dim FolderName

'move to first sheet
Sheets("New Drive Index").Select

'select exising paths and delete
Range("b6:f65000").ClearContents

'open folder in b4
Set fs = Application.FileSearch
FolderName = Sheets("New Drive Index").Range("b4").Value

'find all files in folder and sub-folders
With Application.FileSearch
.NewSearch
.LookIn = FolderName
.SearchSubFolders = False

subfolder = MsgBox("Seach Subfolders as well?", vbYesNo)
If subfolder = vbYes Then
.SearchSubFolders = True
Else
End If
.FileName = "*.*"

'tell how many files there are
If .Execute() > 0 Then
[f4].Select
ActiveCell.Formula = .FoundFiles.Count
[b6].Select
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
'show the paths

Range("b6").Select

Application.ScreenUpdating = False
For i = 1 To .FoundFiles.Count


FileNameOnly = Dir(.FoundFiles(i))


ActiveCell.Formula = FileNameOnly

ActiveCell.Offset(0, 2).Formula = FileDateTime(.FoundFiles(i))

ActiveCell.Offset(0, 1).Value = FileLen(.FoundFiles(i)) / 1000
ActiveCell.Offset(0, 3).Value = .FoundFiles(i)

ActiveCell.Offset(0, 4).Value = .BuiltinDocumentProperties.Item("author").Value
ActiveCell.Offset(1, 0).Select



Next i

Else
MsgBox "There were no files found."
End If
End With
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True



End Sub

 

[fumei]

ActiveCell.Offset(0, 4).Value = .BuiltinDocumentProperties.Item("author").Value

is the builtinproperty of.....what? Nothing it seems.

You should take up the suggestion and include the use of DSO.

faq219-2884

Gerry
My paintings and sculpture