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
617 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
  • 7
  • 6
14 Comments
 
LVL 52

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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
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…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

930 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now