Filename displayed on charts 2

[emfraser]

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!

 

[IRstuff]

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

 

[emfraser]

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!

 

[IRstuff]

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

 

[MinProc]

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.

 

[IRstuff]

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

 

[emfraser]

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!