Solved

Need a script to print files in a specific folder and move the printed files to another folder

Posted on 2011-09-28
14
623 Views
Last Modified: 2012-05-12
Hello,

I need to automate a process to print all the files in a folder and then move the printed files to another folder.

Thank you
S
0
Comment
Question by:Sean
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 54

Expert Comment

by:Bill Prew
ID: 36719544
What type of files are they, just TXT, or something else?

~bp
0
 
LVL 1

Author Comment

by:Sean
ID: 36719560
sorry,
Excel and txt. Mostly Excel.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36744748
Hi, try this out.

Regards,

Rob.
strSource = "C:\Source"
strDestination = "C:\Destination"

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Right(strDestination, 1) <> "\" Then strDestination = strDestination & "\"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

For Each objFile In objFSO.GetFolder(strSource).Files
	blnPrinted = False
	Select Case LCase(objFile.Extension)
		Case "txt"
			objShell.Run "NOTEPAD /P """ & objFile.Path & """", 1, True
			blnPrinted = True
		Case "xls"
			Set objWB = objExcel.Workbooks.Open(objFile.Path)
			objWB.PrintOut
			objWB.Close False
			blnPrinted = True
	End Select
	If blnPrinted = True Then
		objFSO.MoveFile objFile.Path, strDestination
	End If
Next
objExcel.Quit

WScript.Echo "Finished."

Open in new window

0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
LVL 1

Author Comment

by:Sean
ID: 36775047
Hi Rob,
good to hear from you.
Please see the error message.
Also, I will need to schedule this job to run and will need to point it to a network printer or could send it to the default printer on the server but if at all possible I like to hard code the printer path.

Thank you
Sean
error1.jpg
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36777651
Hi Sean,

There is no line 31.......did you use the Select All button on the code snippet?

For Excel, you could print to a specific printer by changing
                  objWB.PrintOut

to
                  objWB.PrintOut , , , , "\\server\printer"

but for notepad, I don't think that's an option.....might need to open the text document in Microsoft Word instead.....if you want to.

Regards,

Rob.
0
 
LVL 1

Author Comment

by:Sean
ID: 36784013
Sorry Rob, not sure what I am doing wrong. And of course don't know what this means
"Select All button on the code snippet".
I am getting some prints but they are all just characters and are not legible.
Also, the files are not being moved.

Sean
error2.jpg
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36787125
OK, I see....change this:
      Select Case LCase(objFile.Extension)

to this
      Select Case LCase(objFSO.GetExtensionName(objFile.Path))

Regards,

Rob.
0
 
LVL 1

Author Comment

by:Sean
ID: 36812669


Hi  Sir Rob,

Almost there. It printed and moved 2 out of 7 excel files. Text file not an issue here none exists in this folder. Please see attached.
Thank you
error3.jpg
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36812691
OK, well I've never been a fan of moving files while inside a file iteration loop, so let's do the moving after all the files have been printed.

Regards,

Rob.
strSource = "C:\Source"
strDestination = "C:\Destination"

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Right(strDestination, 1) <> "\" Then strDestination = strDestination & "\"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strPrintedFiles = ""
For Each objFile In objFSO.GetFolder(strSource).Files
	blnPrinted = False
	Select Case LCase(objFile.Extension)
		Case "txt"
			objShell.Run "NOTEPAD /P """ & objFile.Path & """", 1, True
			blnPrinted = True
		Case "xls"
			Set objWB = objExcel.Workbooks.Open(objFile.Path)
			objWB.PrintOut , , , , "\\server\printer"
			objWB.Close False
			blnPrinted = True
	End Select
	If blnPrinted = True Then
		If strPrintedFiles = "" Then
			strPrintedFiles = objFile.Path
		Else
			strPrintedFiles = strPrintedFiles & "|" & objFile.Path
		End If
	End If
Next
objExcel.Quit

For Each strFilePath In Split(strPrintedFiles, "|")
	objFSO.MoveFile strFilePath, strDestination
Next

WScript.Echo "Finished."

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 36812779
Sorry Rob. same error but this time on line 14 and the characters are back.
s
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 36812985
Doh!  I swear I changed that line before I posted.....


strSource = "C:\Source"
strDestination = "C:\Destination"

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Right(strDestination, 1) <> "\" Then strDestination = strDestination & "\"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strPrintedFiles = ""
For Each objFile In objFSO.GetFolder(strSource).Files
	blnPrinted = False
	Select Case LCase(objFSO.GetExtensionName(objFile.Path))
		Case "txt"
			objShell.Run "NOTEPAD /P """ & objFile.Path & """", 1, True
			blnPrinted = True
		Case "xls"
			Set objWB = objExcel.Workbooks.Open(objFile.Path)
			objWB.PrintOut , , , , "\\server\printer"
			objWB.Close False
			blnPrinted = True
	End Select
	If blnPrinted = True Then
		If strPrintedFiles = "" Then
			strPrintedFiles = objFile.Path
		Else
			strPrintedFiles = strPrintedFiles & "|" & objFile.Path
		End If
	End If
Next
objExcel.Quit

For Each strFilePath In Split(strPrintedFiles, "|")
	objFSO.MoveFile strFilePath, strDestination
Next

WScript.Echo "Finished."

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 36813142
Rob,
Looks like that did it.
I will try it in production tomorrow.
Saved my behind. Thank you.
If I may ask another favor and its not too much work, would it be possible to have the script create the "destination" folder like this,  i.e "printed-09292011",  09292011 being the date the script was executed?

Thanks again.
Sean
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 36813156
Sure, that's easy enough.  Try this.

Rob.
strDate = Right("0" & Month(Date), 2) & Right("0" & Day(Date), 2) & Year(Date)
strSource = "C:\Source"
strDestination = "C:\Printed-" & strDate

Set objShell = CreateObject("WScript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Right(strDestination, 1) <> "\" Then strDestination = strDestination & "\"
If objFSO.FolderExists(strDestination) = False Then objFSO.CreateFolder(strDestination)

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

strPrintedFiles = ""
For Each objFile In objFSO.GetFolder(strSource).Files
	blnPrinted = False
	Select Case LCase(objFSO.GetExtensionName(objFile.Path))
		Case "txt"
			objShell.Run "NOTEPAD /P """ & objFile.Path & """", 1, True
			blnPrinted = True
		Case "xls"
			Set objWB = objExcel.Workbooks.Open(objFile.Path)
			objWB.PrintOut , , , , "\\server\printer"
			objWB.Close False
			blnPrinted = True
	End Select
	If blnPrinted = True Then
		If strPrintedFiles = "" Then
			strPrintedFiles = objFile.Path
		Else
			strPrintedFiles = strPrintedFiles & "|" & objFile.Path
		End If
	End If
Next
objExcel.Quit

For Each strFilePath In Split(strPrintedFiles, "|")
	objFSO.MoveFile strFilePath, strDestination
Next

WScript.Echo "Finished."

Open in new window

0
 
LVL 1

Author Closing Comment

by:Sean
ID: 36819049
Thank you Rob.

Sean
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Swadhin
Introduction of Lists in Python: There are six built-in types of sequences. Lists and tuples are the most common one. In this article we will see how to use Lists in python and how we can utilize it while doing our own program. In general we can al…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question