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

 

[stdz]

Hello Erim,

Welcome to the board !

There was this post a little earlier which should solve the problem !

http://www.mrexcel.com/board2/viewtopic.php?t=187681

Ciao,

Don.

 

[ERIM]

Bonjourno stdz

Many thanks for your extremely prompt response to my post.

However, I came across the same (or similar) post to which you have brought my attention, and tried an adapted version of it. What I'm trying to do is to read in the spreadsheet CREATION DATE and store it in a macro variable - not a cell on the spreadsheet. I will then extract the YYMMDD elements of the date and add it to a variable containing the string that will become my SAVE AS filespec.

The code I used was as follows:

cdt = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")

but cdt is empty after invoking this line of the macro.

I presume the code contained in the post you referenced works, so why doesn't the above work?

Regards

 

[stdz]

Hi Erim,

This worked for me

Sub tst()
Dim cdt
cdt = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
Debug.Print cdt

End Sub

Result of Debug.Print:

09/01/2006 12:18:18

Ciao,

Don.

 

[ERIM]

Hi stdz

I'm afraid I had no such luck!

I copied your code into a new macro, opened the file from which I'm trying to extract the creation date, then ran your code.

The macro stopped at the line starting with 'cdt = ' accompanied by the following error dialog:

Run-Time error '-2147467259 (80004005)':

Automation error
Unspecified error

I recall that this happened when I ran the original sample code I included in my original post which prompted me to include the 'On error' line.

I've tried looking for some explanation regarding this error but Microsoft's database includes hundreds of errors containing the 80004005 code, none of which seem to include my problem!

Are we running different versions of Excel, or do you have an add-in that I don't?

Regards

 

[stdz]

Hi Erim,

I'm running W2K and XL2K.

Maybe you're missing a reference (IDE==>Tools==>References), here's what I have:

Visual Basic for Applications
Microsoft Excel 9.0 Object Library
OLE Automation
Microsoft Office 9.0 Object Library

Ciao,

Don.

 

[ERIM]

Hi Stdz

I've since discovered that if I run your TST macro against another spreadsheet, I don't see the error, and receive the correct date information. Thus, it can't be anything to do with the elements listed in your last post, and must be the way I'm processing the files in my VBA code.

My macro processes all the files it finds in a particular directory (placed there by my MS_Project VBA macro). The macro might find anything from 0 to 50+ files that require processing (depending on how busy I've been in Project).

To process the unknown number of files encountered, I make use of the following code (this is the relevant bits at the start and end of the macro and doesn't include DIM statements and code not relevant to this problem):

    Set fs = Application.FileSearch
    
    dr = "t:\Documents & Spreadsheets\Project Summary Data"
    drp = dr & "\Ready for printing"
     
    With fs
        .LookIn = dr
        If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & " file(s) found."
            For i = 1 To .FoundFiles.Count
                
                 Workbooks.Open .FoundFiles(i)
                
                delfl = .FoundFiles(i)
                
'                cdt = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
                savfl = Mid(delfl, 49, Len(delfl) - 48)
     
            '------main processing loop follows

after the main body of processing the reformatted file is saved with a new name and closed, and the original file is deleted. I then loop back and process the next file.

           '   write file to 'For Printing' directory
            
            ChDir drp
            rfld = drp & savfl
            ActiveWorkbook.SaveAs Filename:=rfld _
                , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
                ReadOnlyRecommended:=False, CreateBackup:=False
            ChDir dr
            
            '   close workbook and delete file
            
            ActiveWorkbook.Close (True)
            
            Kill delfl
            
            Next i
        Else
            MsgBox "There were no files found."
        End If
    End With

I presume that as I'm in a FOR/NEXT loop in the APPLICATION.FILESEARCH this is what's causing the error shown in my previous post. Do you think my assumption could be correct? If so, do you know of any other way that gives the same result, but allows me to capture the input file creation date?

Regards

 

[stdz]

Hi Erim,

Just a thought based on your last post:

What kind of files are you opening ? If they are not Excel workbooks then the "Active" workbook (as yet not saved) will likely not have a "creation" date until it is saved (==> created ). Is the creation date important or do you just want to create unique filenames ?

Ciao,

Don.

 

[ERIM]

Good Morning stdz

Although I mentioned in my last post that the files I'm processing are output from Project, they are in fact proper Excel workbooks (.XLS), and can be opened as you might expect. The creation date is available to me on opening each file (FILE>PROPERTIES).

I want to use the creation date in the SAVE AS filename to allow multiple versions of projects chronologically ordered and immediately visible. It's important that I use the extracted date created from each source file, as I run the macro in question infrequently (i.e. approx once a month), so I need the date that the file was output from Project, not the date I run the macro.

Regards

 

[stdz]

Good Morning to you too Erim !

I'm afraid that I don't have Project available on my PC but I'm starting to doubt that the XLS workbooks created by it are really 100% native Excel. The fact that my TST sub worked OK on your machine with an original Excel XLS would suggest that this might be the case. Looking at the VB help files it appears that there a three flavors of DocumentProperties - one set for (BuiltIn) Excel, another for (BuiltIn) Office (mso) and lastly Custom. I suspect that Project is using mso so I would check if you have a reference to the libraries that I posted yesterday, especially:

Microsoft Office 9.0 Object Library

Nothing else comes to mind at present !

Ciao,

Don.