creating filename

[Andrew]

How can I create a file name based on the entries in the "properties" section
of a document?

 

[Ivyleaf]

Hi Andrew,

ThisWorkbook.BuiltinDocumentProperties(PropertyName) will read the
property from the file... use CustomDocumentProperties for a custom
property, but be aware that this will return an error if the property
doesn't exist.

Once you have the properties read, just join 'em up however you want
to make the filename. I would advise though that you sick in some well
thought out error handling code to cater for:

Properties not existing
Properties being null length (if it hasn't been filled out for some
reason)
Properties containing characters that will be invalid in a file
name... eg. you might want to do a Replace(NewFileName,"/","-") or
something similar to ensure the save won't fail.

Hope this helps.

Cheers,
Ivan.

 

[Ivyleaf]

This code from Chip Pearson will help you with the property names:

Sub Properties()
Dim N As Long
On Error Resume Next
With ThisWorkbook.BuiltinDocumentProperties
For N = 1 To .Count
Cells(N, 1).Value = .Item(N).Name
Cells(N, 2).Value = .Item(N).Value
If Err.Number <> 0 Then
Cells(N, 2).Value = CVErr(xlErrNA)
Err.Clear
End If
Next N
End With
End Sub

(Thanks Chip)

Cheers,

 

[Andrew]

Hi and thanks for the reply.

Not sure if I don't understand cause I'm a novice or if I didn't give enough
info.

I want to run a macro that will create the file name based on entries in the
properties section of a document. The file name composed of (for example):

Subject_Category_Title_status.xls

sorry if I did not give enough info at the begining.

 

[Ivyleaf]

Hi Andy,

This isn't perfect, but I think it should give you a starting point.
Note that the "status" property is actually a 'custom' property and
hence will generate an error if it has not been initialised.

Dim errMsg() As String

Sub BuildFName()
Dim FName As String
ReDim errMsg(0)

On Error Resume Next

With ThisWorkbook
'Get Subject
FName = .BuiltinDocumentProperties("Subject")
If Err.Number Then ErrHandler ("Subject")

'Add Category
FName = FName & "_" & .BuiltinDocumentProperties("Category")
If Err.Number Then ErrHandler ("Category")

'Add Title
FName = FName & "_" & .BuiltinDocumentProperties("Title")
If Err.Number Then ErrHandler ("Title")

'Add Status
FName = FName & "_" & .CustomDocumentProperties("Status")
If Err.Number Then ErrHandler ("Status")

'Add extension
FName = Replace(FName, "/", "-") & ".xls"
End With

If Not ((Not errMsg) = True) Then
For i = 1 To UBound(errMsg)
errText = errText & IIf(i = 1, "", " & ") & errMsg(i)
Next
End If

MsgBox "Filename will be: " & FName & " but there were errors with
" & errText
End Sub

Sub ErrHandler(Property As String)
On Error Resume Next
Dim NewSize As Integer

NewSize = UBound(errMsg) + 1
ReDim Preserve errMsg(0 To NewSize)
errMsg(UBound(errMsg)) = Property
Err.Clear
End Sub

Cheers,
Ivan.

 

[Andrew]

I'll try it

thanks
--
agibson

Hi Andy,

This isn't perfect, but I think it should give you a starting point.
Note that the "status" property is actually a 'custom' property and
hence will generate an error if it has not been initialised.

Dim errMsg() As String

Sub BuildFName()
Dim FName As String
ReDim errMsg(0)

On Error Resume Next

With ThisWorkbook
'Get Subject
FName = .BuiltinDocumentProperties("Subject")
If Err.Number Then ErrHandler ("Subject")

'Add Category
FName = FName & "_" & .BuiltinDocumentProperties("Category")
If Err.Number Then ErrHandler ("Category")

'Add Title
FName = FName & "_" & .BuiltinDocumentProperties("Title")
If Err.Number Then ErrHandler ("Title")

'Add Status
FName = FName & "_" & .CustomDocumentProperties("Status")
If Err.Number Then ErrHandler ("Status")

'Add extension
FName = Replace(FName, "/", "-") & ".xls"
End With

If Not ((Not errMsg) = True) Then
For i = 1 To UBound(errMsg)
errText = errText & IIf(i = 1, "", " & ") & errMsg(i)
Next
End If

MsgBox "Filename will be: " & FName & " but there were errors with
" & errText
End Sub

Sub ErrHandler(Property As String)
On Error Resume Next
Dim NewSize As Integer

NewSize = UBound(errMsg) + 1
ReDim Preserve errMsg(0 To NewSize)
errMsg(UBound(errMsg)) = Property
Err.Clear
End Sub

Cheers,
Ivan.