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
625 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 56

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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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 …
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

615 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