Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
628 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 59

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 2000 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
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…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
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…

972 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