Excel 2007

[sameerkol007]

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

[mancubus]

crossposted here:

http://www.excelforum.com/excel-programming-vba-macros/869592-change-author-name-in-all-excel-files.html

pls provide links to the threads in the message when posting to multiple forums:
http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

[GarysStudent]

In column A enter the full filespec of the files you wish to change:

C:\TestFolder\junk\file1.xlsx
C:\TestFolder\junk\file2.xlsx
C:\Users\James\Desktop\Book.xlsx

Then run this macro:

Sub FixAuthor()
Dim N As Long, L As Long, s As String
N = Cells(Rows.Count, 1).End(xlUp).Row
For L = 1 To N
s = Cells(L, 1).Value
Workbooks.Open Filename:=s
With ActiveWorkbook
.BuiltinDocumentProperties(3) = "James"
.Save
.Close
End With
Next
End Sub

[sameerkol007]

i need to give the path & other details in the columns.

I have around 3000 files. its not possible to give all the paths.

pls give some alternate way to this.

Thanks in advance

please help

[GarysStudent]

How are the paths organized?

[sameerkol007]

for this issue my brother has given me a code which runs on excel 2000 and 2002 but it does not work on 2007.

please modify the macro code.

the code is

Option Explicit

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


please help

[sameerkol007]

i need help anyone can please help

[GarysStudent]

Your macro will not work in Excel 2007 because Application.FileSearch was removed:

http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6