Link to home
Start Free TrialLog in
Avatar of spaced45
spaced45Flag for United States of America

asked on

Extract document properties from files stored in Sharepoint libraries into Excel

Experts,
I want to scan multiple SharePoint document libraries and extract those document's properties. It would be great if I could get that data into an Excel document. I am trying to implement an organizing structure to new documents placed on our SharePoint site. New docs are no problem since I have already made certain fields required but for existing document it’s another story. I know I can get into a Data view and just find the docs with the missing info but there’s just way to many folders and sub sites. Any help would be greatly appreciated.
Thanks
Avatar of rspahitz
rspahitz
Flag of United States of America image

I'm not all that familiar with sharepoint.  if it acts like a windows directory structure, then it should be rather easy to do certain things.  You'd use something in VBA like this:

dim strFile as string
strFile =dir("sharpointpath")
do until strFile = ""
   if FileLen(strFile)>0 then
' do something with this file
  end if

   if FileDateTime(strFile)<now() then
' do something else with this file
   end if

' other checks on the file information
 
   dir
loop

Avatar of spaced45

ASKER

Yep structure is pretty much the same but I would still need the script to read document properties. For example customer fields, username, created date, etc. I remember seeing a post a while back that did something similar but I havent been able to locate it any where. The result was an excel file showing the file path, name and some other common fields like created date. Dont know if it was possible to get any others though.
Well, you can also play tricks with things in Windows, like use DOS to show additional information, redirected to a file, then load the file, all through VBA code.
Otherwise, you could also use the FileSystemObject to load additional things.

First add a reference to Microsoft Scripting Runtime in Tools | References
then code like this:

Dim fso As FileSystemObject
Dim objFile As File

strFilename = "put some file name here or use the DIR code previously shown"
Set fso = New FileSystemObject
With fso
    Debug.Print .GetAbsolutePathName
    Debug.Print .GetBaseName
    Debug.Print .GetExtensionName
    Debug.Print .GetFileVersion
End With
   
Set objFile = fso.GetFile(strFilename)
With objFile
    Debug.Print .DateCreated
    Debug.Print .Size
    Debug.Print .Type
End With

So this might work a bit better:

sub ShowFileInfo()

Dim fso As FileSystemObject
Dim objFile As File
Dim strFilename As String

strFilename = FileSystem.Dir("*.*")
Set fso = New FileSystemObject

do until strFilename = ""
With fso
    Debug.Print .GetAbsolutePathName(strFilename)
    Debug.Print .GetBaseName(strFilename)
    Debug.Print .GetExtensionName(strFilename)
    Debug.Print .GetFileVersion(strFilename)
End With
   
Set objFile = fso.GetFile(strFilename)
With objFile
    Debug.Print .DateCreated
    Debug.Print .Size
    Debug.Print .Type
End With
strFilename = FileSystem.Dir
Loop

End Sub
rspahitz,
Thanks for the suggestion. I will try it out now.
I am get a message that says Compliation Error. Something about an expected object on line three. Let me know if I did this right but I placed the code in Notepad and saved it on my desktop as a .vbs file. Is that right?
I guess you missed this from my previous comment:


First add a reference to Microsoft Scripting Runtime in [menu] Tools | References
...

See if that helps.  If not, you can directly reference it but I'll have to look up the library where it sits.
I am sorry I dont want to sound like a complete moron here but am suppost to be pasting the code into an excel sheet or in a .vbs script file. I pasted it into Excel and on the line that says:
strFilename = FileSystem.Dir("*.*")
I replaced with a directory path between the quotes. Good news is that I got zero errors. Bad news is that nothing happened.
ASKER CERTIFIED SOLUTION
Avatar of rspahitz
rspahitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hey hey!! You were right I did need step four. This works perfectly. I really appreciate the help.
Glad to help out.  I assumed you knew about all these things but the clue should have been "vbs", which handles things differently. :)
Avatar of TWISTED_IT
TWISTED_IT

It works well with certain document properties but does not extract the version.
so where would you like to go next with this?
i tried the code above which works well with most properties but what i am after is to get the version, it returns the version if the file is on ntfs but blank if  the file is in a doc library in Sharepoint. i would like to get the version of a document in sharepoint whether major or minor based on version control/history being enabled in Sharepoint and WITHOUT  the file being opened. We have a user interface in Word 2007 developed to list all files from a document library, i want to be able to click on a document and say view version - to view latest version without having to load the version history from the portal. Does that make sense?
I don't think there's a guaranteed way to determine the version of a file (or even an application.)  However, depending on the app (like Office products) you may find the version embedded at the beginning of the document.
There's also the "version control" version, but that depends on the product you are using (and maybe Sharepoint is one but I'm not familiar with its inner workings)
ok thanks. versions are automatically stored within the version history of a particular document in word. you can see it from the document's revision number property but when it is stored within sharepoint, you can embed the version info within the document, display it as a form field or label within the footer for example, but it means you have to see it within the document, perhaps within the sharepoint forum section there might be an answer. thanks for replying