How can I print Excel spreadsheet files automatically?

Question

My company has computer-generated reports that come out as Excel spreadsheets which are emailed to the Billing Department. They would like these reports to be automatically printed as they are received.

I already found a program that will automatically download email attachments, called Mail Attachment Downloader.

Now ideally, I would want a program which will:

1. Scan a chosen folder for new XLS spreadsheets that are downloaded.
2. Print them out.
3. Get rid of them (preferably by moving them to a different folder).

I know I can just write a batch file to take care of #3; and #1 isn't really necessary. I could just have a program run at a certain time to automatically print out any files it sees, then follow it with a batch file to move the files somewhere else. But finding a program that will automatically print Excel spreadsheets has proven to be difficult.

I have seen some Excel macros that automatically print a spreadsheet, but I want it to automatically open them and print them all by itself.

One of the challenges I'm up against is the Billing Department doesn't have Microsoft Excel yet. In the very near future, they will have a computer with Excel 2010, but for now it just has LibreOffice.

Solution 1

I used AutoIt to make a script that opens Excel, opens the only file (which begins with "elec") in a specified folder (which begins with "viat"), prints it, closes Excel, then runs a batch file to archive the file it just printed.

Here is the code of the AutoIt script file. I was banging my head on this for a while until I discovered that I needed the Sleep commands to make it work properly.

Run("C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe")
WinWaitActive("Microsoft Excel")
Send("^o")
WinWaitActive("Open")
ControlClick("Open", "", 1148)
Send("c:\viat{DOWN}{ENTER}")
Sleep(1000)
Send("elec")
Sleep(1000)
Send("{DOWN}{ENTER}")
WinWaitActive("Microsoft Excel")
Send("^p")
Sleep(1000)
Send("{ENTER}")
Sleep(1000)
WinClose("Microsoft Excel")
Sleep(1000)
Run(""C:\Users\Chris\Documents\ViaTrack Archives\archiver.bat"")

And here is the code of archiver.bat:

move "C:\ViaTrack Reports\*.*" "C:\Users\Chris\Documents\ViaTrack Archives"

It's extremely simplistic coding, but it gets the job done. The main drawback of this script is that it will cause Excel to throw an error if the file does not exist in the folder. However, it is scheduled to run about an hour after the file is normally emailed to me, to allow for any unexpected delays.

The file is normally emailed at 5:40 AM every weekday. Historically, it's never arrived later than 5:45. Mail Attachment Downloader is set up to check for new attachments every 15 minutes.

It worked just fine this morning. The Billing Department Manager was happy to come in and find that her report was waiting for her on the printer, and she didn't have to print it out herself.

Solution 2

If Excel is installed on the system that you want to print them from, then you can use Excel automation from scripting (which you can launch from, or instead of, your batch file).

Here's a couple examples in VBScript, and PowerShell:

VBscript .VBS (found here):

Dim XLApp
Dim XLWkbk
Dim ObjArgs

set ObjArgs = wscript.arguments
if ObjArgs.count <> 1 then
wscript.echo "Invalid passed arguments"
wscript.quit
end if

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = False

Set XLWkbk = XLApp.Workbooks.Open(objargs(0))
XLWkbk.PrintOut
XLWkbk.Close False

XLApp.Quit

Set XLWkbk = Nothing
Set XLApp = Nothing
Set ObjArgs = nothing

PowerShell .PS1 equivalent:

$excelApp = New-Object -comobject Excel.Application

if ($args.Length -lt 1)
{
  Write-Host "Please provide full path and filename (ie: `"c:\books\excelfile.xlsx`")"
  Exit
}

$excelBook = $excelApp.Workbooks.Open($args[0])
$excelBook.PrintOut()
$excelApp.Quit()

Comments

[MaQleod]

Look into AutoIt. It has an Excel library for automating anything within Excel, it also has file management functions for checking for files, deleting files as necessary, printing files, etc. To use the Excel specific functions, Excel needs to be installed, but to just print a file, it doesn't need anything special installed.

[Chris]

That did it :) I installed AutoIt and made some scripts to print out Excel spreadsheets, and set up Windows Task Scheduler to run them at the appropriate times.