Solved

Reading Office 2007 document Properties from Access 2007 code without opening the document (e.g. Word 2007).

Posted on 2009-03-30
9
580 Views
Last Modified: 2013-11-27
I want to read the Properties (Author, Title, Subject etc.) of Office documents such as Word 2007, Excel 2007 and PowerPoint 2007 without opening them, from VBA code in Access 2007. Is this possible and if so, how? For the sake of discussion, it would probably be best to take the case of reading document properties for a Word 2007 file (call it: "TestDoc.xlsx" located in a folder "C:\TestFolder"). I appreciate any help on this. Thanks.
0
Comment
Question by:DataTrain
  • 5
  • 4
9 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Hello DataTrain,

The following post shows how to use the DSO OLE Document Properties Reader 2.0 library to get at document
properties without actually opening them:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22022458.html#17720721

It will require downloading a DLL from here: http://support.microsoft.com/?id=224351

Warning: while this works with Office 2003 and earlier, I have not tested it using the new OpenXML file types
from Office 2007...

Regards,

Patrick
0
 

Author Comment

by:DataTrain
Comment Utility
Hi. I downloaded the DLL and then added it using Tools menu, References and ticked "DSO OLE Document Properties Reader 2.1" However, it fails at the first line...
"Dim fso As Scripting.FileSystemObject"
with Compile error: User-defined type not defined. Was this code designed for Access 2007?
Note also that I my objective is to copy the Property data into an Access table fields.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
DataTrain,

Sorry!  Add a reference to the Microsoft Scripting Runtime library; that is the source for the FileSystemObject
class and its related classes.

Regards,

Patrick
0
 

Author Comment

by:DataTrain
Comment Utility
That helps a bit but I now I get Run-time error 3192, could not find output table 'FileStuff".
Any explanation you can give about how this is supposed to work would be helpful as I am unfamiliar with some of your code. Thanks.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
DataTrain,

Please post the code you have so far.

Regards,

Patrick
0
 

Author Comment

by:DataTrain
Comment Utility
I was just using your code...
Sub FileStuff()
 

    Dim fso As Scripting.FileSystemObject

    Dim fld As Scripting.Folder

    Dim fil As Scripting.File

    Dim dsofil As DSOFile.OleDocumentProperties

    

    Set fso = New Scripting.FileSystemObject

    Set fld = fso.GetFolder("C:\Folder\folder\subfolder")

    Set dsofil = New DSOFile.OleDocumentProperties

    

    DoCmd.SetWarnings False

    

    For Each fil In fld.Files

        dsofil.Open fil.Path, True, dsoOptionDefault

        DoCmd.RunSQL "INSERT INTO FileStuff (FileName, Folder, Size, Created, Modified, Title, " & _

            "Subject, Author) Values ('" & fil.Name & "', '" & fil.ParentFolder.Path & "', " & _

            fil.Size & ", #" & fil.DateCreated & "#, #" & fil.DateLastModified & "#, '" & _

            dsofil.SummaryProperties.Title & "', '" & dsofil.SummaryProperties.Subject & "', '" & _

            dsofil.SummaryProperties.Author & "')"

        dsofil.Close

    Next

    

    DoCmd.SetWarnings True

    

    Set dsofil = Nothing

    Set fil = Nothing

    Set fld = Nothing

    Set fso = Nothing

    

End Sub

Open in new window

0
 

Author Comment

by:DataTrain
Comment Utility
The only difference was that I cahnged:
Set fld = fso.GetFolder("C:\Folder\folder\subfolder")
to
Set fld = fso.GetFolder("C:\Test")
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
Comment Utility
DataTrain,

The example I posted assumed that there is a table in the database, FileStuff, with columns for FileName,
Folder, Size, Created, Modified, Title, Subject, and Author.

Are you looking to store file information in a table?  If so, what is the name of that table, and what are the
columns?

Regards,

Patrick
0
 

Author Closing Comment

by:DataTrain
Comment Utility
I appreciate your help - thank you.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PaperPort (http://www.nuance.com/for-individuals/by-product/paperport/index.htm) is among the most important applications that I run on my Windows computers. I use it every day, for nearly all of my document and photo scanning, as well as most of my…
In a previous article here at Experts Exchange (http://www.experts-exchange.com/articles/18414/Create-a-PDF-file-with-Contact-Sheets-montage-of-thumbnails-for-all-JPG-files-in-a-folder-and-each-of-its-subfolders-using-an-automated-batch-method.html)…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

762 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

12 Experts available now in Live!

Get 1:1 Help Now