PDA

View Full Version : Sleeper: Macro to Change Properties in all Files in a Folder



Anne Troy
02-09-2005, 08:07 PM
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
02-09-2005, 09:05 PM
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
02-09-2005, 09:20 PM
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
02-09-2005, 09:28 PM
You're Welcome :)

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

Desert Piranha
08-21-2005, 10:27 AM
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
08-21-2005, 11:13 AM
This code as written only works for files in the directory of the current workbook. Is that where you are looking?

Desert Piranha
08-21-2005, 02:11 PM
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?