Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-03-30
9
Medium Priority
?
633 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 93

Expert Comment

by:Patrick Matthews
ID: 24022459
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
ID: 24027148
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 93

Expert Comment

by:Patrick Matthews
ID: 24027866
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 

Author Comment

by:DataTrain
ID: 24028131
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24028451
DataTrain,

Please post the code you have so far.

Regards,

Patrick
0
 

Author Comment

by:DataTrain
ID: 24028657
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
ID: 24028698
The only difference was that I cahnged:
Set fld = fso.GetFolder("C:\Folder\folder\subfolder")
to
Set fld = fso.GetFolder("C:\Test")
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1000 total points
ID: 24029539
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
ID: 31564500
I appreciate your help - thank you.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

824 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