Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
donnerj

donnerj

MIS
Joined
Nov 11, 2003
Messages
2
Location
CA
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 ?
 
Sort by date Sort by votes
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.)
 
Upvote 0 Downvote
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").Select
Selection.Cut Destination:=Range("B1")
Columns("B:B").Select
Selection.Cut
Columns("A:A").Select
ActiveSheet.Paste
Range("F1").Select
Selection.Cut Destination:=Range("E1")
Range("G1").Select
Selection.Cut Destination:=Range("H1")
Columns("C:E").Select
Range("E1").Activate
Selection.Cut
Range("B1").Select
ActiveSheet.Paste
Columns("H:I").Select
Selection.Cut
Range("E1").Select
ActiveSheet.Paste
Range("K1").Select
Selection.Cut Destination:=Range("J1")
Columns("J:J").Select
Selection.Cut
Columns("G:G").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
Range("M1").Select
Selection.Cut Destination:=Range("L1")
Columns("G:G").Select
Selection.TextToColumns Destination:=Range("G1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(8, 2), Array(25, 9)), TrailingMinusNumbers:= _
True
Range("G1").Select
ActiveCell.FormulaR1C1 = "Date of Sterilization"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Time of Sterilization"
Columns("L:L").Select
Selection.Cut
Columns("I:I").Select
ActiveSheet.Paste
Rows("1:1").Select
Range("B1").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").Activate
Cells.EntireColumn.AutoFit
Columns("G:I").Select
Range("I1").Activate
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("B:B").Select
With Selection
.HorizontalAlignment = xlRight
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:1").Select
Range("B1").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").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").Select
ActiveWindow.FreezePanes = True
Cells.Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("D2") _
, Order2:=xlAscending, Key3:=Range("F2"), Order3:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
Range("A1").Select
Selection.AutoFilter
ActiveCell.SpecialCells(xlLastCell).Select
Columns("J:N").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.AutoFilter
' Calc number of rows to copy down formulas
Range("D1").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").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").Select
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Serial #"
Rows("1:1").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").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").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").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").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").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").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").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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

BxWill
  • Question Question
MS EXCEL 2016 LINE CHART NOT DROP TO ZERO - USING NESTED IF AND COUNTIF
Replies
1
Views
56
combo
combo
Swi
  • Locked
  • Question Question
Excel Webservice
Replies
2
Views
811
mintjulep
M
Kennelbloke
  • Locked
  • Solved
Excel Unique-Filter issue
Replies
3
Views
784
SkipVought
S
Kim296
  • Question Question
Need Genius Help - Finding multiple duplicates between columns - EXCEL
2
Replies
38
Views
3K
mintjulep
M
Cpreston
  • Locked
  • Question Question
Excel Networking days
Replies
4
Views
684
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top