Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
Using VBA you 2 options:
- Open the WB and use ThisWorkbook.BuiltinDocumentProperties ThisWorkbook.CustomDocumentProperties - Use something like DSO http://www.microsoft.com/downloads/d...DisplayLang=en If you are looking for WS names, you could maybe use ADO/ADOX to get inof on the "database" structure. NickHK "JVLin" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
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 "JVLin" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
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 If you are looking for WS names, you could maybe use ADO/ADOX to get inof on the "database" structure. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading/Changing Workbook Propeties with VBA?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading values from a closed workbook | New Users to Excel | |||
Excel document propeties in Cells | Excel Discussion (Misc queries) | |||
Reading Cells is the same workbook different tab | Excel Discussion (Misc queries) | |||
Reading a worksheet from a specified workbook | Excel Worksheet Functions | |||
Reading from a closed workbook | Excel Programming |