Custom Document Properties in Word documents and Excel workbooks

J

johnacooke

Does anyone know how to read the Custom Document properties of a Word document or Excel workbook from VBA without opening the workbook or Word document?

This can be done interactively manually by right-clicking on the file name and selecting Properties | Custom on the dialog box that appears. This doesnot involve opening the file.

However, does anyone know if there is a way of accomplishing the above without opening the file?

I need to a hierarchical directory tree-walk on a folder that contains manythousands of Excel workbooks and Word documents, looking for specific Custom Document properties. I can do so, if I open and close the files, but this is very long-winded and might not be necessary.

I should say that I use Custom Document properties extensively in Excel.

Any insights would be appreciated. Thank you.
 
R

Ron Rosenfeld

Does anyone know how to read the Custom Document properties of a Word document or Excel workbook from VBA without opening the workbook or Word document?

This can be done interactively manually by right-clicking on the file name and selecting Properties | Custom on the dialog box that appears. This does not involve opening the file.

However, does anyone know if there is a way of accomplishing the above without opening the file?

I need to a hierarchical directory tree-walk on a folder that contains many thousands of Excel workbooks and Word documents, looking for specific Custom Document properties. I can do so, if I open and close the files, but this is very long-winded and might not be necessary.

I should say that I use Custom Document properties extensively in Excel.

Any insights would be appreciated. Thank you.
Click to expand...

There's a DLL mentioned on Chip Pearson's site, but it doesn't work for Excel 2007 or later. But see here: http://technet.microsoft.com/en-us/library/ee176615.aspx for a method of doing this through the shell.
 
I

isabelle

hi,

this macro works on xl2010

Sub Macro1()
Dim arrHeaders(35)
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace("C:\Users\Public\Documents")
For i = 0 To 34
arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
Next
For Each strFileName In objFolder.Items
For i = 0 To 34
Debug.Print arrHeaders(i) _
& ": " & objFolder.GetDetailsOf(strFileName, i)
Next
Next
End Sub

isabelle
 
G

GS

I think the key words in the OP's Q are "Custom Document Properties".
Note that the suggested approach only works with "Extended File
Properties"!

This task requires the OLE File Property component named "dsofile.dll".
To work with Office12 and later will also require the Office 2007
Compatibility Pack.

I'm not sure, but I think you may also be able to do this via ADODB.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
R

Ron Rosenfeld

I think the key words in the OP's Q are "Custom Document Properties".
Note that the suggested approach only works with "Extended File
Properties"!
Click to expand...

Good Point
 
G

GS

GS wrote on 09/02/2013 :
I think the key words in the OP's Q are "Custom Document Properties". Note
that the suggested approach only works with "Extended File Properties"!

This task requires the OLE File Property component named "dsofile.dll". To
work with Office12 and later will also require the Office 2007 Compatibility
Pack.

I'm not sure, but I think you may also be able to do this via ADODB.
Click to expand...

You can download dsofile here...

http://support.microsoft.com/kb/224351

...which includes samples and source code.

You can get the Office Compatibility Pack here...

http://www.microsoft.com/en-us/download/details.aspx?id=3

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

Thanks, Isabelle! Good article. What surprises me is that msoshext.dll
doesn't ship with Office NOR dsofile.dll since it's dependant for use
with MSO12 and later.

I find the msoshext.dll here...

C:\Program Files\Common Files\Microsoft Shared\OFFICE12

...on my XP machine. I haven't installed the Compatibility Pack on my
Win7 Pro x64 machine as yet and so can't say where it installs. (I
assume Program Files (x86)\Common Files\Microsoft Shared\OFFICE12)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

I just installed the Office 2007 Compatibility Pack on my Win7 Pro x64
machine and it put msoshext.dll here...

C:\Program Files\Common Files\Microsoft Shared\OFFICE12

...NOT under Program Files (x86) as I assumed!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

CellShocked

Thanks, Isabelle! Good article. What surprises me is that msoshext.dll
doesn't ship with Office NOR dsofile.dll since it's dependant for use
with MSO12 and later.

I find the msoshext.dll here...

C:\Program Files\Common Files\Microsoft Shared\OFFICE12

..on my XP machine. I haven't installed the Compatibility Pack on my
Win7 Pro x64 machine as yet and so can't say where it installs. (I
assume Program Files (x86)\Common Files\Microsoft Shared\OFFICE12)
Click to expand...

The Microsoft page I saw said it was good for OSes up to Vista (so I
did Not DL it), so maybe this is addressed in a different manner in
Windows 7 systems, and that is why it is no longer simply provided or
integrated in?
 
G

GS

CellShocked formulated the question :
The Microsoft page I saw said it was good for OSes up to Vista (so I
did Not DL it), so maybe this is addressed in a different manner in
Windows 7 systems, and that is why it is no longer simply provided or
integrated in?
Click to expand...

I installed the Office 2007 Compatibility Pack and dsofile and all is
working fine on my Win7 Pro x64 machine!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I

isabelle

hi GS,

i think so, on my new pc, this file is located here

C: \ Program Files \ Common Files \ Microsoft Shared \ Office14

isabelle

Le 2013-02-10 04:03, GS a écrit :
 
C

CellShocked

CellShocked formulated the question :

I installed the Office 2007 Compatibility Pack and dsofile and all is
working fine on my Win7 Pro x64 machine!
Click to expand...


I didn't say it wouldn't install or work.

I just mentioned that the DL page stated what OSes it was for, and that
is why I didn't grab it.

I probably already have it and don't know it.
 
G

GS

CellShocked submitted this idea :
I didn't say it wouldn't install or work.

I just mentioned that the DL page stated what OSes it was for, and that
is why I didn't grab it.

I probably already have it and don't know it.
Click to expand...

I would think that at the time the page was written Vista was the
latest OS since Dsofile.dll hasn't been updated since 2007!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
G

GS

isabelle explained on 10/02/2013 :
hi GS,

i think so, on my new pc, this file is located here

C: \ Program Files \ Common Files \ Microsoft Shared \ Office14

isabelle

Le 2013-02-10 04:03, GS a écrit :
Click to expand...

I'm of the opinion that installing to *Program Files* on an x64 machine
is probably forced (ie: hard coded) since most x86 stuff installs under
*Program Files (x86)*!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
C

CellShocked

CellShocked submitted this idea :

I would think that at the time the page was written Vista was the
latest OS since Dsofile.dll hasn't been updated since 2007!<g>
Click to expand...


Ahhh... the old 'forgot to note da age of da page' thing...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Word Template where user inputs <TITLE> and then that can be displayed in footers or headers [MAC] 0
Word Template and Normal Style 1
Excel freezing after sharing document by Email 0
Auto Merge text from multiple sources 0
Embedded Excel into Word with Tables 0
Workbook custom document properties 1
Merge Excel file into Word Document 3
Extract embedded excel file from word document 0

Top