Sleeper: Macro to Change Properties in all Files in a Folder

[Anne Troy]

Hi, guys!

I need a macro to change the author and company name of all the files in a folder to Anne Troy, VBA Express.

Of course, this would also make a terrific KB entry. :)

I'm desperate for it ASAP. (as usual)

[Jacob Hilderbrand]

Try this.


Option Explicit

Sub ChangeAttributes()
Dim FileName As String
Dim Path As String
Dim Wkb As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False
Path = ThisWorkbook.Path
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWorkbook.Name Then
On Error Resume Next
Set Wkb = Workbooks.Open(FileName:=FileName)
On Error GoTo 0
End If
If Not Wkb Is Nothing Then
Wkb.BuiltinDocumentProperties("Author") = "Anne Troy"
Wkb.BuiltinDocumentProperties("Company") = "VBA Express"
Wkb.Close SaveChanges:=True
Set Wkb = Nothing
End If
FileName = Dir()
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Alternately you could just do this.

Select all the files



Right Click



Properties



Change everything as needed



Ok

[Anne Troy]

Yeah. I didn't wanna change each one, and changing all at once wouldn't work. :)
I'm sure the code will! I'll run it tomorrow. (I thought I'd be done tonight, but I won't.)

Thanks, Jake. You doll!

[Jacob Hilderbrand]

You're Welcome :)

Let me know if it doesn't work for you.

[Desert Piranha]

DRJ,
Are there any updates to this code? I been trying to use it but have had no success. When i run the code, the mouse pointer turns to an hourglass for about one second, then quits, nothing gets changed in the properties of workbooks in the same folder.
thx
Dave

Try this.


Option Explicit

Sub ChangeAttributes()
Dim FileName As String
Dim Path As String
Dim Wkb As Workbook
Application.ScreenUpdating = False
Application.EnableEvents = False
Path = ThisWorkbook.Path
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
If FileName <> ThisWorkbook.Name Then
On Error Resume Next
Set Wkb = Workbooks.Open(FileName:=FileName)
On Error GoTo 0
End If
If Not Wkb Is Nothing Then
Wkb.BuiltinDocumentProperties("Author") = "Anne Troy"
Wkb.BuiltinDocumentProperties("Company") = "VBA Express"
Wkb.Close SaveChanges:=True
Set Wkb = Nothing
End If
FileName = Dir()
Loop
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


Alternately you could just do this.

Select all the files



Right Click



Properties



Change everything as needed



Ok

[Bob Phillips]

This code as written only works for files in the directory of the current workbook. Is that where you are looking?

[Desert Piranha]

xld,
Is "Directory" and "Folder" the same thing? If so, then Yes, the xls files are in the same Directory as the workbook with the code.
thx
Dave



This code as written only works for files in the directory of the current workbook. Is that where you are looking?