excel 2003 Reading workbook properties / details from an XLA how do you do it?

[brad]

Reading workbook properties from an XLA

Hi,

Hope you can help me on this one.
I have a Software Package that exports excel workbooks containing
worksheets with data in an unformatted state,
I am using excel 2003 and have written a VBA formatting macro which
works fine to format this raw data (colours, borders, applys some
formulas etc)
What i want to do is distribute this macro so that when a user opens
up one of these raw data spreadsheets, if certain criteria are matched
then format it automatically using my macro.

The criteria could be
a) if a custom property exists that we have set (part of the File-

properties->Custom Tab)

or
b) in some instances on export we have no control on the exported
excel file, so there are no custom properties so we must check on
certain worksheet names, cell values to see if they contain specific
data (i.e. column headings - ok not elegant but headings will be very
unique)

Thus what I want to do is do this via an xla file.
So I saved my VBA macro as an XLA
I then added the event Sub auto_open() to the xla code
within this even i added some code to do the criteria checks
I then placed the xla into the following directory

C:\Program Files\Microsoft Office\OFFICE11\XLSTART

Anything placed in here runs whenever excel opens (i.e. i cannot
expect the user to "add in" something manually via addins i want it
automated), therefore what i did was in the on_open event, was to do
my criteria checks however at this point the workbook is not present
i.e. you cannot do activeworkbook. or thisworkbook. as it refers to
the xla not the file i am opening.

Hence whereas the code below to retrieve a custom property will work
in a normal workbook, it wont when in an xla, i need somehow to get a
handle on the workbook i am opening..

On Error Resume Next
Application.ActiveWorkbook.CustomDocumentProperties("HasBeenFormatted").Value
On Error Goto 0

--this will fail in an xla, i want it to get the custom properties of
the book i am opening...

Any ideas, appreciated
thanks

Brad

 

[Guest]

Brad: There is a count propety that will prevent the error. I'm not sure if
"HasBeenFormatted" will be a value or name or something else. the solution
will be similar to the code that is shown below.

if Application.ActiveWorkbook.CustomDocumentProperties.count > 0 then

or
for each prop in Application.ActiveWorkbook.CustomDocumentProperties
if prop.value = "HasBeenFormatted" then
'add your code here
end if
next prop


to find this property this is what I did

sub test()
set x = ActiveWorkbook
end sub

I added x as a watch property and stepped through the code. Then I look at
the watch window. I found in the watch CustomDocumentProperties. Under
CustomDocumentProperties ther is a count. I know from similar problems I
encounted that you have to test the count property otherwise you get errors
like the one you are encountered.