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
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
RE: Filename displayed on charts
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
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
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
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!