Sub EECHARTS()
'
' EECHARTS Macro
' AUTO CHART EE OVERALL DIAMETER DATA
'
' Keyboard Shortcut: Ctrl+p
'
Dim oBook as WorkBook
'Start Loop
For Each oBook in Workbooks
' Rename Sheet
ActiveSheet.Name = "Data"
'
' Set Cell D1 Equal to the File Name
Range("$D$1") = oBook.Name
Range("D2").Select
ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[-1]C,"".CSV"","""")"
Range("D2").Select
Selection.Copy
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues
Range("D2").Select
Selection.ClearContents
'
' Select Data Automatically
Range("B2:C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Name = "TableRange"
'
' Chart Data Selected Above
Charts.Add
ActiveChart.ApplyCustomType ChartType:=xlUserDefined, TypeName:= _
"Macro Chart"
ActiveChart.SetSourceData Source:=Sheets("Data").Range("TableRange"), _
PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart.TextBoxes.Add(338, 229, 49, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$C$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft -75#
Selection.ShapeRange.IncrementTop -191.52
With ActiveChart.TextBoxes.Add(338, 229, 34, 15)
.Select
.AutoSize = True
.Formula = "='Data'!$D$1"
.Font.Bold = True
.Font.Size = 14
.Font.ColorIndex = 3
End With
Selection.ShapeRange.IncrementLeft 136#
Selection.ShapeRange.IncrementTop -191.52
'
' Print Chart
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'
' Save File
If oBook.FileFormat = xlCSV Then
oBook.SaveAs FileFormat:=xlNormal
End If
'
' Close Workbook
oBook.Close
'
' Run Macro for all Open Files
Application.OnTime Now + TimeValue("00:00:01"), "EECHARTS"
next 'oBook
'
End Sub