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:
- Scan a chosen folder for new XLS spreadsheets that are downloaded.
- Print them out.
- 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.
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.
