×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Filename displayed on charts
2

Filename displayed on charts

Filename displayed on charts

(OP)
I made a macro to display several charts based on a single run on a machine. The filenames of the Excel files are saved as the date of the run and the materials used (i.e. 030708N.xls would be July 8, 2003 Nitrogen. Is there any way for a macro to display the name of the file on the graph so when it prints it's right there? Thanks!

RE: Filename displayed on charts

This is what the macro recorder recorded when I formatted for date, time, filename and tab name:

    With ActiveChart.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = "&8&d &t\&f-&a"
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .ChartSize = xlFullPage
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .BlackAndWhite = False
        .Zoom = 100
    End With

the right footer uses:
&d - date
&t - time
&f - file
&a - tab
&8 - 8pt font

Remember that pretty much anything that you can do from the user interface can be recorded by the macro recorder.  it's a gooood thing

TTFN

RE: Filename displayed on charts

(OP)
IRStuff -- thanks for your quick reply! Unfortunately, the date of the run (recorded in the actual filename) is different from the date I might be analyzing the data, so the Date and Time functions don't really work for me. The Sheet would work, except the sheets are already named with other names. Is there anything that will just put on the Filename? And yes, I use the Macro Recorder extensively. I can't figure out how to get it to record the filename in this case. Thanks for anyone's help!

RE: Filename displayed on charts

I'm not sure I understand your question; it doesn't matter what the sheet or file is called.  &f and &a are variable placeholders, not the actual filename and sheet name.

When you go into the page format from the file menu, you simply type in "&f" and "&a" (without the quotes) in the header or footer and Excel will automatically substitute the filename and sheet name in place of &f and &a.

TTFN

RE: Filename displayed on charts

you might be better off with:

fName = activeworkbook.name

which puts the name of the current workbook (eg. book1.xls)into the variable fName.  You could then put the value in a textbox on each graph.

regarding IRstuff's method, I do know that this option will not work on all versions of excel (older versions don't have an option to include the filename switch for the header/footer)

hope this helps.

RE: Filename displayed on charts

Depends on how old; &f has been around since Excel95.  

And, any older version of Excel doesn't use VBA, so that means there's no way to place a text box programmatically on the chart for that old a version anyway.

TTFN

RE: Filename displayed on charts

(OP)
Thanks to both of you! I tried them out and they worked beautifully!

I ended up using the textbox idea just so I can move it around if I want, once the macro puts it there. Sorry for my post earlier after IRStuff's first post -- I saw the &d and &t for date and time, but somehow I missed the $f in my rush to get out of the lab for the weekend. Sorry for creating more confusion!! Thanks a million!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources