Formatting results of .RightFooter = ActiveWorkbook.Keywords after results pulled

This works at my house:

    ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular""&8" & ActiveWorkbook.Keywords

Kevin

Hi,

Can you try the following....

- Ardhendu

Sub one()
Dim strng
    strng = ActiveWorkbook.Keywords
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Times New Roman,Regular""&12&P"
        .RightFooter = "&""Times New Roman,Regular""&8" & strng
    End With

End Sub

Select allOpen in new window

Kevin,
I didn't see ur comment before i posted. Sorry about that.

Behest,
The points should be awarded to Kevin, as his solution is better than mine.

Thanks,
Ardhendu

Kevin, if only I lived in your 'hood. I'm using Office 2007 (fyi). Although the formatting works, the ActiveWorkbook.Keywords chunk doesn't pull the keywords property. Instead, I see a blank right footer.
I've tried
.RightFooter = ActiveWorkbook.Keywords"&""Times New Roman,Regular""&8"
and many other variations. I even hoped (in vain) that I could pull the keywords field in one line, then go back and format the results. My clumsy method for writing that bit didn't work.
Is there perhaps a way to pull the field, then go back and have whatever results are pulled formatted appropriately?

Hi Behest,

Did you try the code that I posted? It worked for me on a Excel 2007 file.

- Ardhendu

I am doing my tests in 2007. No issues.

What is the error?

Kevin

Try using ThisWorkbook instead of ActiveWorkbook:

    ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords

Kevin

Better still, reference the specific worksheet:

    ThisWorkbook.ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords

Kevin

ok...if I do a very basic script:

Sub mine()
    With ActiveSheet.PageSetup
        .RightFooter = ActiveWorkbook.Keywords
    End With
End Sub

the result is whatever data has been added to the Document Properties keywords field.
I've tried each of your suggestions provided and the keywords field is not returned using these methods.

Use syntax:

   ThisWorkbook.ActiveSheet.PageSetup.RightFooter = "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords

Kevin

Tried it...results in a blank right footer.
First try, I set up a new sub and pasted in the code. blank...
Second try, I broke it into a With statement...

Sub kevin3()
    With ThisWorkbook.ActiveSheet.PageSetup
        RightFooter = "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords
    End With
End Sub
 
Again, blank.
So I pulled out the formatting detail...

Sub kevin3()
    With ThisWorkbook.ActiveSheet.PageSetup
        .RightFooter = ThisWorkbook.Keywords
    End With
End Sub
And the keywords data shows up in the footer.
Question 1...do you have the keywords field populated?
Question 2...is there a way to format the variable results after the fact?
Question 3...Why me?

OK, this is weird. You sure you are not on a Mac?

Try this:

   Dim Text As String
   Debug.Print "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords
   Debug.Print "&""Times New Roman,Regular""&8"
   Debug.Print ThisWorkbook.Keywords
   Text = "&""Times New Roman,Regular""&8" & ThisWorkbook.Keywords
   Debug.Print Text
   Debug.Print "" & ThisWorkbook.Keywords
   Debug.Print "x" & ThisWorkbook.Keywords

Tell me what gets dumped.

Kevin

What, exactly, is in Keywords?

Kevin

I just have some numbers in Keywords...specifically, 12121212 because I was practicing my counting. lol.
I'm not on a Mac, but I know how to work on them. Surely the computer can't detect that when I'm in this mode. who knows...
Set up your Dim text code as a separate sub and the footer is blank in print preview and when I print to a pdf.
What should I have seen?

Mac - that was a joke dude!

So does the code work if you put in something like "abc, def, xyz" for the keywords?

Kevin

All better!!! Kevin, thank you for your patience, knowledge, and humor.
It just occurred to me that one of the Add-Ins I have was active and possibly causing the conflict. Something in there conflicted with your code. So I de-activated it and my world is a much happier place.
Thank you VERY much!

Thought I'd post a follow-up that deals with how Excel treats numbers in a Document Property field. The results I was trying to pull will all be numeric. When tested, the point size of the field results in the footer was something like 300 pts. The quick answer was to add a space to the front of the keywords field (i.e., "12345" becomes " 12345".
Since this will be applied to thousands of files, I ended up add a space to another of the Document Property fields and pulling both of them in the footer. Looks like this...

Sub CombProp()
ActiveWorkbook.BuiltinDocumentProperties("Category").Value =  "  "
    With ActiveSheet.PageSetup
        .LeftFooter = "&""Times New Roman,Regular""&8" & _
            ActiveWorkbook.BuiltinDocumentProperties("Category") & _
            ActiveWorkbook.BuiltinDocumentProperties("Keywords")
    End With

End Sub