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.
On Apr 1, 1:11*pm, Andrew <and...@discussions.microsoft.com> wrote:
> 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.
> --
> agibson
>
>
>
> "Ivyleaf" wrote:
> > 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,
>
> > On Apr 1, 12:28 pm, Ivyleaf <ica...@gmail.com> wrote:
> > > 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.
>
> > > On Apr 1, 12:00 pm, Andrew <and...@discussions.microsoft.com> wrote:
>
> > > > How can I create a file name based on the entries in the "properties" section
> > > > of a document?
> > > > --
> > > > agibson- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -
Similar Threads
