BuiltinDocumentProperties doesn't return expected data

ERIM

New Member
Joined
Sep 30, 2005
Messages
21
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
 
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.
 
Upvote 0

Forum statistics

Threads
1,226,867
Messages
6,193,428
Members
453,799
Latest member
shanley ducker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top