script to print out a folder FULL of excel files

Scripting Forums AppleScript | Mac OS X
#1

Hi…
does anyone have a suggestion the best way to print out a folder contain 400 excel files. should/can i do this w/ a shell script or applescript. does anyone have one? i assume i can’t use excel w/ a pipe. any suggest would be GREAT.
thanks

#2

What kind of printout are you interested in creating? A list of the filenames? A stack of full spreadsheets? Summaries of the documents?

casdvm

#3

i want to print the (contents) excel file to the printer (lp) the excel file contains images…

so what i need is open xxx.xls, print to lp) close, open next file, print, close, open next file, print, close, open next file and so on…

#4

Vixster:

I have never scripted Excel, but this script uses commands from the Microsoft Excel 2004 Applescript PDF document that is
available on the Microsoft website.

For this script to work, you need to move your folder with all the xls files to your Desktop, and then hope that I got all the commands right.

set main_path to path to desktop
set fol_der to choose folder --This is the folder where all the Excel files are located

tell application "Finder"
	set scat_files to the name of every file in folder fol_der whose name extension is "xls"
end tell

repeat with pi from 1 to (count scat_files)
	set open_file to (main_path & fol_der & (item pi of scat_files)) as Unicode text
	tell application "Microsoft Excel"
		activate
		open workbook workbook file name open_file
		print out active sheet
		close every workbook saving no
	end tell
end repeat

Good luck with this, let me know if it doesn’t work, I can play around with Excel; I do have it but I very seldom use it.

casdvm

#5

HEY casdvm
you rock…
at first it didn’t work… had trouble with opening/reading the file. so i edited a bit and now it works.! I have never ever done an apple script, still don’t fully understand it but it’s my bsh,csh scripting that helped me here.
thanks so much!!! your really really deserve an applaud!!! clap, clap… okay, so i am a little excited… thanks

this is the final

set main_path to path to desktop
set fol_der to choose folder --This is the folder where all the Excel files are located

tell application “Finder”
set scat_files to the name of every file in folder fol_der whose name extension is “xls”
end tell

repeat with pi from 1 to (count scat_files)
set open_file to (item pi of scat_files) as Unicode text
tell application “Microsoft Excel”
activate
open workbook workbook file name open_file
print out active sheet
close every workbook saving no
end tell
end repeat

#6

casdvm,

how do i put a wait command in it after each print… it’s going to fast for usb printers.
thanks
vixster

#7

this is too darn weird, all of a sudden, it can’t open the excel file

what could have changed in my system to make this behavor like this

error message “Microsoft excel got an error: Can’t continue open workbook”

first i was up, now i’m down.

#8

Vixster:

No problem, I think we can work through this. There have been some recent posts on waiting for a command to finish before going to the next one, and I have been reading those, so we can try some stuff for that.

I am not sure you pasted your new code in your 5:30:16 posting, it looks exactly like what I posted before. Please check that first, and re-post the new code so we can move forward from there.

Understand also that this is a Microsoft app; it will probably be quirky to some extent.

casdvm

#9

After learning a bit AppleScript I fixed the script, so it runs now with Excel 365.
IMPORTANT
Every Excel file you want to print need to start with ~$ so you don’t need to allow excel to open each file. Also you need to creat a a VBA Marco in Excel and save it to your personal file PERSONAL.XLSB. The macro is Simple and is one Module. My name for it is Print1. The Code for the macro is:

Sub Print1()
ActiveWindow.SelectedSheets.PrintOut copies:=1

End Sub

The new script is now:

set main_path to path to desktop
set fol_der to choose folder --This is the folder where all the Excel files are located
set vba_script to choose file
tell application "Finder"
	set fl to (files of folder fol_der whose name extension is "xlsx") as alias list
end tell

repeat with f in fl
	tell application "Microsoft Excel"
		activate
		open ex_script
		open f
		run VB macro "'~$PERSONAL.XLSB'!Print1"
		close every workbook saving no
	end tell
end repeat