Concatinating strings in a footer

  • Thread starter Thread starter Sierk
  • Start date Start date
S

Sierk

I am having a problem with the delopment of some code to construct a footer.
By creating a macro I got the following:

ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftFooter = "&""Times New Roman,Regular""&8Path String&F"
End With

I am trying to construct a footer string on the fly, concatenating the font
formatting ("&""Times New Roman,Regular""&8), the Path String and the auto
file name (&F")

I tried several things including assigning the entire string to a variable
causing Excel to ignore the control (&) characters as well as assigning the
string components to several variables causing Excel's VBA engine to cough up
a hairball. Any ideas?
 
Sub formatt()
Dim s As String
dq = Chr(34)
pound = "&"
s = pound & dq & "Times" & dq & "Hello World"
ActiveSheet.PageSetup.CenterHeader = s
End Sub

As you can see, the only tricky point is getting the double quote (dq) in
the proper place in the string.
 
Thanks. It makes sense now. The confusion came from me not understanding
the function of the control characters:
So
.LeftFooter = pound & dq & "Times" & dq & "Hello World" would actually
translate into:
.LeftFooter = "&""Times""Hello World"

This puts "Hello World" (without quotes) in your footer using the Times font
 
The confusion is not your problem alone.

I can never remember whether to type a single double-quote, two
double-quotes, or three double-quotes.

That why I usually use the variable dq
 
Back
Top