Dear Everyone,
I used to work in a computer in which all the excel files has the author name as the Computer name which i want to change to my name.
I have around 3000 Excel files having the author name as "Sachin" i want to change the author name of all the files to "Bikash".
Please help how can i do this .
Regrads
Bikash Shaw
Please Login or Register to view this content.
Please Login or Register to view this content.
So, you can say, for example:
Please Login or Register to view this content.
You just need some code to loop through all the Excel files in a folder and set property 3 to your name.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I don't agree with changing a workbooks author, but this is how you do it.
Please Login or Register to view this content.
Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
I am the real 'Napster'
The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...
If you receive help please give thanks. Click the * in the bottom left hand corner.
snb's VBA Help Files
@JapanDave: I don't think it is ... that's the user name, not the author of the workbook, necessarily.
Regards, TMS
I disagree.
You need a loop built around
myfile = DIR(path\*.xl*,vbNormal)
to fetch file names within a directory until myfile returns null
That has to be wrapped inside a loop to fetch the folders ( ,vbDirectory)
then inside the innernost loop
activeworkbook.builtinproperties.author = "Bikash Shaw"
activeworkbook.save true
And it is right to change the author, except that this sledge hammer will do ALL spreadsheets; you might want to be more selective than that.
Last edited by brynbaker; 10-22-2012 at 02:48 PM.
Hey brynbake
Can you please write the whole code in the post.
The author name need to be " BIkash Shaw"
please help
Please help me by writing the whole code
i am getting confused by the above conversation
pls help me
I got a code in google serach for the above but it does not work in office 2007
can u please help me with this
The code is as follows
Sub ChangeLotsOfFilesProperties()
' Attributes we will be changing
' Author, Title, Comments
Const szAuthor As String = "vbaexpress.com"
Const szTitle As String = "Updated Title"
Const szComments As String = "Batch update code"
Dim szFolderPath As String
Dim objFolder As Object
Dim szbkName As String
Dim lUbk As Long
Dim i As Long
Dim wkb As Workbook
Dim fso As Object
Dim f As Object
' Browse for the folder to search for project workbooks
' ===========================================================================
Set objFolder = CreateObject("Shell.Application"). _
BrowseForFolder(0, _
"Select the folder containing workbooks to update", _
0, Empty)
On Error Goto ErrExit
If Not objFolder Is Nothing Then
szFolderPath = objFolder.items.Item.Path
Else
Exit Sub
End If
' ===========================================================================
With Application
.ScreenUpdating = False
.EnableEvents = False
If Val(.Version) >= 9 Then 'ShowWindowsInTaskbar is for versions 2000+
.ShowWindowsInTaskbar = False
End If
End With
With Application.FileSearch
.NewSearch
.LookIn = szFolderPath
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
.Execute
' if we found some files to update
If .FoundFiles.Count > 0 Then
' Loop through them, changing document properties
For i = 1 To .FoundFiles.Count
Set wkb = Workbooks.Open(.FoundFiles(i))
' Procedure can be lengthy, status bar for updating
Application.StatusBar = "[" & i & " of " & _
.FoundFiles.Count & "] Changing properties for " & wkb.Name
' Late binding reference to the FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(ActiveWorkbook.FullName)
' If the file is Read-Only, don't update it
If f.Attributes And 1 Then
' so close it:
wkb.Close False
Else
' Otherwise change the specific document properties
With wkb
' Props we are changing, Author, Title, Comments
.BuiltinDocumentProperties("Author") = szAuthor
.BuiltinDocumentProperties("Title") = szTitle
.BuiltinDocumentProperties("Comments") = szComments
' Store the workbook names we update in a variable
' This will be used to deliver our final message
szbkName = szbkName & vbNewLine & wkb.Name
' Then save and close
.Save
.Close
End With
End If
Next i
Else
MsgBox "No files found to update", 64
End If
End With
ErrExit:
' Explicitly clear memory
Set wkb = Nothing
Set fso = Nothing
Set f = Nothing
Set objFolder = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
If Val(.Version) >= 9 Then 'ShowWindowsInTaskbar is for versions 2000+
.ShowWindowsInTaskbar = True
End If
.StatusBar = Empty
End With
MsgBox "* Properties have been changed for these Files: *" & szbkName, 64
End Sub
I'm on the case
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.
In view of the read only issue, you'll understand I've not tested this thoroughly.Please Login or Register to view this content.
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.
I am getting run time error 53
file not found.
and i am working in excel 2007.
please help
Your last post reported an error and I assume it is in the code I posted not what you got from google. (you did not say where/why/how your googled code failed.
I'm debugging now. The problem is getting the right number of \ in the recursion. Be right back... (hopefully)
OK. I have it.
The first is a calling routine; specify the path where you want to start (needs \ at the end). It will run through all files and folders below in that path.Please Login or Register to view this content.
It is generic and can be used by anyone for any similar all the files in the subfolders too operation. It calls the third macro to do the work on the files. The macro gets all the files and must select which ones to amend; I used a SELECT clause.
It has been a heavy thread. There are dozens of code clips around the read only the current folder but very few that parse the tree as this does. I hope others will find it valuable. (Post edited)
Last edited by brynbaker; 10-25-2012 at 06:41 AM.
Since this thread is about BuiltinDocumentProperties, is there a way I can set the BuiltinDocumentProperties Comments of an Excel file to Read only after updating the comments in the VBA code.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks