Ah, now this is embarrassing. It tuns out that (in excel 2003 anyway) ActiveWorkbook.BuiltinDocumentProperties.Author is read only. I don't have 2010 handy to check it there. But the code follows for instruction anyway. There are othings it CAN do.
Sub doAllAuthors() ' a routine to start the process
Dim done As Long
parseFolders "c:\", "*.xls*", 0
MsgBox "Done " & done & " replacements"
End Sub
Sub parseFolders(strFolder As String, strFileSpec As String, done) ' a subroutine that ripples through the sub folders
' This uses recursion; not always best in VB but fine here where levels are few
' start at folder (must have a \ at the end)
folder = "C:\"
ChDir strFolder ' ready to open any files in this folder
strFile = Dir(strFolder & strFileSpec, vbNormal)
Do While strFile > ""
' process files until all done
Workbooks.Open strFile
' could call code to do anything but we will display the author name (as its readonly)
msgbox ActiveWorkbook.BuiltinDocumentProperties.Author
ActiveWorkbook.Close True
done = done + 1 ' count processed files
strFile = Dir() ' next file
Loop
'Build collection of additional subfolders.
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
' folder contains entries to be ignored (parent and current)
If (strTemp <> ".") And (strTemp <> "..") Then
' check valid dolder
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
' recurively call
parseFolders strFolder & "\" & strTemp, strFileSpec, done
End If
End If
strTemp = Dir ' next in directory
Loop
End Sub
In view of the read only issue, you'll understand I've not tested this thoroughly.
If 2010 does not help, you have to do it by hand I fear. Ho hum.
You could change your name to that of the old company, perhaps? OK probably not.
Bookmarks