Solved

Extract document properties from files stored in Sharepoint libraries into Excel

Posted on 2011-02-10
16
915 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:spaced45
  • 8
  • 5
  • 3
16 Comments
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 
LVL 1

Author Comment

by:spaced45
Comment Utility
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
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 
LVL 1

Author Comment

by:spaced45
Comment Utility
rspahitz,
Thanks for the suggestion. I will try it out now.
0
 
LVL 1

Author Comment

by:spaced45
Comment Utility
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
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 
LVL 1

Author Comment

by:spaced45
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
Comment Utility
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
 
LVL 1

Author Closing Comment

by:spaced45
Comment Utility
Hey hey!! You were right I did need step four. This works perfectly. I really appreciate the help.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
Glad to help out.  I assumed you knew about all these things but the clue should have been "vbs", which handles things differently. :)
0
 

Expert Comment

by:TWISTED_IT
Comment Utility
It works well with certain document properties but does not extract the version.
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
so where would you like to go next with this?
0
 

Expert Comment

by:TWISTED_IT
Comment Utility
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
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
 

Expert Comment

by:TWISTED_IT
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now