Reading/Changing Workbook Propeties with VBA?

[Guest]

Hi,

is it possible to change/add properties on the 'Custom' tab using VBA? (I've
tried to use the VB recorder, but it comes up empty.)

Similary, is it possible to read from the 'Contents' properties tab using
VBA, e.g. the names of worksheets or named ranges?

Context:
I'm still not satisfied with the speed at which I get certain basic info
from an Excel file and I was hoping that by writing such info to the file's
properties and reading them without opening the workbook I might speed things
up.

Many thanks for your help on this.

Regards,
JVL

 

[NickHK]

Using VBA you 2 options:
- Open the WB and use
ThisWorkbook.BuiltinDocumentProperties
ThisWorkbook.CustomDocumentProperties

- Use something like DSO
http://www.microsoft.com/downloads/...c6-520b-4a0a-878a-53ec8300c4c2&DisplayLang=en

If you are looking for WS names, you could maybe use ADO/ADOX to get inof on
the "database" structure.

NickHK

 

[Guest]

Sorry for the reply to self, but ...

I found something in Excel Help under:
- DocumentProperties Collection Object
- BuiltinDocumentProperties Property
- CustomDocumentProperties Property

Specifically:
"Using the DocumentProperties Collection
Use the Add method to create a new custom property and add it to the
DocumentProperties collection. You cannot use the Add method to create a
built-in document property.

Use BuiltinDocumentProperties(index), where index is the index number of the
built-in document property, to return a single DocumentProperty object that
represents a specific built-in document property. Use
CustomDocumentProperties(index), where index is the number of the custom
document property, to return a DocumentProperty object that represents a
specific custom document property."

SO: it appears that custom properties can be added and read using VBA code.

I have yet to figure out though whether a 'ContentsDocumentProperties'
object exists which contains sheet and range names (it appears not) and
whether any of this will speed things up.

Regards,
JvL

 

[NickHK]

Yes, but to use those properties, you need a reference to the workbook,
which you obtaining by opening the file, something I thought you wanted to
avoid.

NickHK

 

[Guest]

Hey NickHK,

thanks for replying.

Yeah, you're right: WB must be opened to read properties, though there is a
PropertyTest property which doesn't require you to open the WB. (See function
from Professional Excel Development (Bullen, Bovey, Green) below.)

I looked at the DSO page referring to the OLE IPropertyStrorage interface,
but this is a bit above my head, since I don't have knowledge of C++ / Visual
Studio to understand the sample code.

Can you (or someone else) give me an example of how to extract worksheet
names using ADO?

Regards,
JvL


With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = sDirectory
'Having the given Yes/No property set to Yes
.PropertyTests.Add sProperty, msoConditionIsYes,
Connector:=msoConnectorAnd
.Execute
FileHasYesProperty = .FoundFiles.Count > 0
End With

 

[Guest]

Thanks again for DSO link:

I had a look at the DSO FilePropDemo (actually VB6 and VB7); It seems to use
this to open the file properties in read-only mode:

Private m_oDocumentProps As DSOFile.OleDocumentProperties

m_oDocumentProps.Open sFile, fOpenReadOnly, _
dsoOptionOpenReadOnlyIfNoWriteAccess

This seems to work faster than opening an entire workbook and you can read a
lot of properties, including custom properties (though I have seen sheet or
range names referred to in the code).

I'll play around with it and see where it gets me.

JvL