[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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

Well, I'm feeling mighty stumped! I finally got the file keywords to appear in a footer, but no of the font formatting combinations I add to the line are working. Lots of "Complie errors" = massive frustration! Below is the essence of the simple code...please help me figure out what I'm doing wrong. As you can see, I've commented out the formatting but wanted you to see it.

With ActiveSheet.PageSetup
         .LeftFooter = "&""Times New Roman,Regular""&12      &P"
'        .RightFooter = "&""Times New Roman,Regular""&8"

        .RightFooter = ActiveWorkbook.Keywords
0
behest
Asked:
behest
  • 9
  • 6
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
This works at my house:

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

Kevin
0
 
Ardhendu SarangiSr. Project ManagerCommented:
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

Open in new window

0
 
Ardhendu SarangiSr. Project ManagerCommented:
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

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
behestAuthor Commented:
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?
0
 
Ardhendu SarangiSr. Project ManagerCommented:
Hi Behest,

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

- Ardhendu
0
 
zorvek (Kevin Jones)ConsultantCommented:
I am doing my tests in 2007. No issues.

What is the error?

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Try using ThisWorkbook instead of ActiveWorkbook:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Better still, reference the specific worksheet:

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

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
Ardhendu,

The Keywords property is typed as a string. Your code produces the same result as:

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

End Sub

Kevin
0
 
behestAuthor Commented:
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.
0
 
zorvek (Kevin Jones)ConsultantCommented:
Use syntax:

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

Kevin
0
 
behestAuthor Commented:
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?
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
What, exactly, is in Keywords?

Kevin
0
 
behestAuthor Commented:
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?
0
 
zorvek (Kevin Jones)ConsultantCommented:
Mac - that was a joke dude!

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

Kevin
0
 
behestAuthor Commented:
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!
0
 
behestAuthor Commented:
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
 
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now