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
622 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Sequence is something that used to store data in it in very simple words. Let us just create a list first. To create a list first of all we need to give a name to our list which I have taken as “COURSE” followed by equals sign and finally enclosed …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Learn the basics of strings in Python: declaration, operations, indices, and slicing. Strings are declared with quotations; for example: s = "string": Strings are immutable.: Strings may be concatenated or multiplied using the addition and multiplic…
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…

740 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