Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Extract document properties from files stored in Sharepoint libraries into Excel

Posted on 2011-02-10
16
933 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
ID: 34866852
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
ID: 34866986
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
ID: 34867087
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:rspahitz
ID: 34867174
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
ID: 34870762
rspahitz,
Thanks for the suggestion. I will try it out now.
0
 
LVL 1

Author Comment

by:spaced45
ID: 34870830
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
ID: 34872257
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
ID: 34874391
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
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 34874715
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
ID: 34874929
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
ID: 34875363
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
ID: 35468820
It works well with certain document properties but does not extract the version.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 35478124
so where would you like to go next with this?
0
 

Expert Comment

by:TWISTED_IT
ID: 35478628
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
ID: 35479723
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
ID: 35481297
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

808 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