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
LVL 1
spaced45Asked:
Who is Participating?
 
rspahitzConnect With a Mentor Commented:
OK, let me make sure you have all the pieces in place, since I'm not sure where the project stands right now.

1) First, the "vbs" stuff should be placed into the macro/VBA area of Excel.  To get there to easy way, press Alt+F11, which will open up the Visual Basic area.

Near the top left corner of that you should see a "Project" panel with something called VBAProject and maybe your sheets.

2) Let's try this way: on the menus, select Insert | Module.  This should open a new code window where you can paste the code.

3) Also in this VB area, go to the menu and select Tools | References.  In the window that appears (with a lot of checkboxes) scroll through the alphabetical listing for "Microsoft Scripting Runtime" and select it then click the [OK] button.

4) The code is now ready to run, but it will put all of the output into an "Immediate" window.  To show that, try Ctrl+G or enable it from the View menu.

--

Now, if you click inside the code block and press F5, it will run for the current directory and put the results into that output window.

I wonder if maybe you need step 4 above.

Once you get that working, we can update the code so you can do several things if you'd like:
1) run it from Excel rather than from the VB area
2) put the results into an excel sheet
3) prompt for a starting directory
etc.


0
 
rspahitzCommented:
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

0
 
spaced45Author Commented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
rspahitzCommented:
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

0
 
rspahitzCommented:
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
0
 
spaced45Author Commented:
rspahitz,
Thanks for the suggestion. I will try it out now.
0
 
spaced45Author Commented:
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?
0
 
rspahitzCommented:
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.
0
 
spaced45Author Commented:
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.
0
 
spaced45Author Commented:
Hey hey!! You were right I did need step four. This works perfectly. I really appreciate the help.
0
 
rspahitzCommented:
Glad to help out.  I assumed you knew about all these things but the clue should have been "vbs", which handles things differently. :)
0
 
TWISTED_ITCommented:
It works well with certain document properties but does not extract the version.
0
 
rspahitzCommented:
so where would you like to go next with this?
0
 
TWISTED_ITCommented:
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?
0
 
rspahitzCommented:
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)
0
 
TWISTED_ITCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.