BuiltinDocumentProperties doesn't return expected data

[ERIM]

Dear Mr. Excel

I'm a fairly experienced Excel user with a background in IT, have automated a number of standard bespoke monthly routines using VBA macros, and have even developed a number of utility macros for all sorts of menial tasks.

I am currently using Excel 2002 SP3 on a Windows XP Professional platform (DELL Laptop).

After discovering your website for the first time early last year, I have used the message board to expand my Excel expertise to new levels, and have yet to be disappointed when looking for ways in which to improve my code. Until, that is, today. Hence this post.

I have a macro which manipulates and adds data to a spreadsheet of data output from MS Project into a format suitable for printing and further use.

I run this process on a number of different files, overwriting older versions of the same output spreadsheet each time. However, I have now decided that it would be useful to keep previous versions of the spreadsheets for changes over time comparison purposes, and have decided the easiest way to do this is to save the reformatted spreadsheet with the file DATE CREATED property included in the SAVE AS new filename spec.

I then searched VBA help and the Mr.Excel message boards to locate a command that would allow me to extract the DATE CREATED and came across the BUILTINDOCUMENT PROPERTIES example:

On Error Resume Next
rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
Cells(rw, 2).Value = p.Value
rw = rw + 1
Next

I included this code while running the macro in debug mode to work out which particular index to use for DATE CREATED, but found that, although p.Name lists all the BUILTINDOCUMENT properties fields available, p.Value does not return a number of the actual property values which are clearly visible in the FILE PROPERTIES dropdown. These include DATE CREATED!

Can anyone out there explain why this is?
Can anyone give me some code that will give me the DATE CREATED value?

I look forward to receiving a reply at your earliest convenience

Kind Regards

 

[ERIM]

Hi stdz

You were quite right to point the finger at the format of the files I am attempting to process.

I have now discovered that the spreadsheets output from Project are in 'MS Excel 5.0/95 workbook' format. Although this doesn't normally cause a problem as Excel 2002 is quite happy to open them as though they are native, it's clear that VBA is less happy in these circumstances!

I have discovered that the file I used yesterday to run the macro TST against, was in fact one that I must have opened in the old format and saved with the current format! Hence the macro worked, when it shouldn't have!

Thus, I have now added some code to my macro to open the old format file and immediately save it in the compatible format. My macro is now able to extract the date created as expected!


So we got there in the end. Many thanks for your assistance in this matter, it has been much appreciated and my faith in Mr.Excel.com is restored.

Regards

John Gale

PS: I did check my references and discovered that I have all those listed (albeit v10 object libaries as I am using XP).

PPS: I envy your location - I have visited Lake Garda on two occasions (Garda and Malcesine) and no doubt will do so again! All the best for 2006.