Open Excel file every day at 6:00 am and print it

[donnerj]

Is there a way to automatically open an excel file every day at 6:00 am and print it out ? Is it possible to do this in a macro somehow or is something more advanced like visual basic required ?

 

[Zathras]

You could use the Windows Task Scheduler to launch the worksheet every moring at 6:00 (or whatever schedule you want) and then use the Workbook_Open event to do whatever you need with VBA.

When setting up the task, don't be put off by Windows asking for a program name. You can just type the fully qualified .xls file name and it will work just fine. (Assuming you have the normal file association set up.)

 

[WildeWebPublishing]

This is the last part of the macro I use.
-----------------------------------------------------------
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.Close
-----------------------------------------------------------
Here is the whole macro- It also calculates the rows as they vairy...
-----------------------------------------------------------
Workbooks.Open Filename:= _
"Your File Here"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1&quot.Select
Selection.Cut Destination:=Range("B1&quot
Columns("B:B&quot.Select
Selection.Cut
Columns("A:A&quot.Select
ActiveSheet.Paste
Range("F1&quot.Select
Selection.Cut Destination:=Range("E1&quot
Range("G1&quot.Select
Selection.Cut Destination:=Range("H1&quot
Columns("C:E&quot.Select
Range("E1&quot.Activate
Selection.Cut
Range("B1&quot.Select
ActiveSheet.Paste
Columns("H:I&quot.Select
Selection.Cut
Range("E1&quot.Select
ActiveSheet.Paste
Range("K1&quot.Select
Selection.Cut Destination:=Range("J1&quot
Columns("J:J&quot.Select
Selection.Cut
Columns("G:G&quot.Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
Range("M1&quot.Select
Selection.Cut Destination:=Range("L1&quot
Columns("G:G&quot.Select
Selection.TextToColumns Destination:=Range("G1&quot, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(25, 9)), TrailingMinusNumbers:= _
True
Range("G1&quot.Select
ActiveCell.FormulaR1C1 = "Date of Sterilization"
Range("H1&quot.Select
ActiveCell.FormulaR1C1 = "Time of Sterilization"
Columns("L:L&quot.Select
Selection.Cut
Columns("I:I&quot.Select
ActiveSheet.Paste
Rows("1:1&quot.Select
Range("B1&quot.Activate
Selection.Font.Bold = True
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Selection.RowHeight = 36.25
With Selection.Font
.Name = "Arial Narrow"
.Size = 11
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Range("B1&quot.Activate
Cells.EntireColumn.AutoFit
Columns("G:I&quot.Select
Range("I1&quot.Activate
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("D&quot.Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B&quot.Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1&quot.Select
Range("B1&quot.Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.AutoFilter
Range("A1&quot.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&P"
.CenterHeader = "&F"
.RightHeader = "&D&T"
.LeftFooter = ""
.CenterFooter = "&Z&F"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
Range("A2&quot.Select
ActiveWindow.FreezePanes = True
Cells.Select
Selection.Sort Key1:=Range("A2&quot, Order1:=xlAscending, Key2:=Range("D2&quot _
, Order2:=xlAscending, Key3:=Range("F2&quot, Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A1&quot.Select
Selection.AutoFilter
ActiveCell.SpecialCells(xlLastCell).Select
Columns("J:N&quot.Select
Selection.Delete Shift:=xlToLeft
Range("A1&quot.Select
Selection.AutoFilter
' Calc number of rows to copy down formulas
Range("D1&quot.Select
Selection.NumberFormat = "General"
Selection.FormulaArray = _
"=MAX(IF(ISBLANK(RC[-1]:R[50000]C[-1]),0,ROW(RC[-1]:R[50000]C[-1])))"
NumRows1 = ActiveCell.Value
Range("E1&quot.Select
Selection.NumberFormat = "General"
Selection.FormulaArray = _
"=MAX(IF(ISBLANK(RC[-1]:R[50000]C[-1]),0,ROW(RC[-1]:R[50000]C[-1])))"
NumRows2 = ActiveCell.Value
ActiveCell.Formula = "Current Location"
Selection.NumberFormat = "@"
Rows(NumRows1 + 1 & ":" & NumRows2).Select
Selection.Delete Shift:=xlUp
Range("A2&quot.Select
Range("D1&quot.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Serial #"
Rows("1:1&quot.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("G1&quot.Select
ActiveCell.FormulaR1C1 = "Date Run"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial Narrow"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H1&quot.Select
ActiveCell.FormulaR1C1 = "Time Run"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial Narrow"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("1:1&quot.Select
Selection.RowHeight = 23
With Selection.Font
.Name = "Arial Narrow"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Cells.Select
Cells.EntireColumn.AutoFit
Selection.Font.Bold = False
Range("F1&quot.Select
ActiveCell.FormulaR1C1 = "Last Scan Loc"
With ActiveCell.Characters(Start:=1, Length:=13).Font
.Name = "Arial Narrow"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("E1&quot.Select
ActiveCell.FormulaR1C1 = "Current Loc"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Arial Narrow"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("I1&quot.Select
ActiveCell.FormulaR1C1 = "Par"
With ActiveCell.Characters(Start:=1, Length:=3).Font
.Name = "Arial Narrow"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1&quot.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = "&P"
.CenterHeader = "&F"
.RightHeader = "&D&T"
.LeftFooter = ""
.CenterFooter = "&Z&F"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWorkbook.Save
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.Close
End Sub

Dave
WildeWebPublishing
The best way to eat chicken is when it is dead.