[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Extract document properties from files stored in Sharepoint libraries into Excel

Posted on 2011-02-10
16
Medium Priority
?
1,030 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
[X]
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
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

649 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