Print multiple excel files using VBscript

Hi Experts,

I have a folder which contains 20 excel files. Each file contains registration data for pupils. One work sheet.
I want a script that will run through the folder and print each file out to the default printer. It would be even better if they could select a specific printer from their list as they might not always want to use the default.

I am hoping somebody has a similar script thay could share.

The computer is windows 7 PRO and they use Office 2007/2010

It would also be useful if a log file could be created saying what file was printed and when.

TIA
osxmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SiddharthRoutCommented:
>>>It would also be useful if a log file could be created saying what file was printed and when.

The log file can give you the time when the print command was issued but not whether the file was actually printed.

Here is a code which I created on the file and it is not tested. Let me know if you get any errors.

UNTESTED

'~~> Folder where the Excel Files are.
'~~> Also this folder is where the log will be created
Const strSourcePath = "c:\Temp\"
'~~> Name of the Log File
Const strFile = "Log.txt"

Dim objFSO, objExcel, objWorkbook, objSheet
Dim objFolder, colFiles, objFile

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objFolder = objFSO.GetFolder(strSourcePath)
Set colFiles = objFolder.Files
Set objFile = objFSO.CreateTextFile(strSourcePath & strFile)

For Each objFile In colFiles
    Set objWorkbook = objExcel.Workbooks.Open(objFile)
    Set objSheet = objExcel.Workbooks(1).Worksheets(1)
    objFile.WriteLine (objWorkbook.Name & " Printed at " & Time)
    '~~> Print to Default Printer
    objSheet.PrintOut
    objWorkbook.Close
Next

objFile.Close

Set objFile = Nothing
Set objSheet = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Open in new window


Sid
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
osxmanAuthor Commented:
Hi Sid, thanks for the reply. When I run the script I get an error:

line 19
char 5
Object doesn't support this property or method "wricteLine"

0
SiddharthRoutCommented:
The syntax is correct

Right now I am on MAC so cannot test it.

try this

objFile.WriteLine("Hello")

Does it work then remove the space after line 19 in the above code after writeline and before the (

Sid
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Robberbaron (robr)Commented:
1/ look at the error again!    wricteLine   you typed it wrong, rather than copy/paste

2/ I have made a few additions to the script.

3/ if you need to change printers, see http://www.robvanderwoude.com/vbstech_ui_defaultprinter.php.  save the current, then check if need to request change back

4/ Some of this would be easier with Excel using VBA,  rather than VBS.
Sub test()

    
    '~~> Folder where the Excel Files are.
    '~~> Also this folder is where the log will be created
    Const strSourcePath = "c:\ee\"
    '~~> Name of the Log File
    Const strFile = "Log.txt"
    
    Dim objFSO, objExcel, objWorkbook, objSheet
    Dim objFolder, colFiles, objFile
    Dim saveprinter
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objExcel = CreateObject("Excel.Application")
    
    saveprinter = objExcel.ActivePrinter
    ChangePrinterSettings
    
    Set objFolder = objFSO.GetFolder(strSourcePath)
    Set colFiles = objFolder.Files
    objFSO.CreateTextFile (strSourcePath & strFile)
    Set objFile = objFSO.GetFile(strSourcePath & strFile)

    ' Open a text stream for output. APPEND
    Set ts = objFile.OpenAsTextStream(ForAppending, TristateUseDefault)
    'Application.ActivePrinter
    For Each objFile In colFiles
        Select Case Left$(objFile.Type, 22)
            Case "Microsoft Office Excel"  'matches all workbook types
                Set objWorkbook = objExcel.Workbooks.Open(objFile)
                Set objSheet = objWorkbook.Worksheets(1)
                ts.WriteLine (objWorkbook.Name & " Printed at " & Time)
                '~~> Print to Default Printer
                objSheet.PrintOut
                objWorkbook.Close False
            Case Else
        End Select
    Next
    
    ts.Close
    
    Set objFile = Nothing
    Set objSheet = Nothing
    Set objWorkbook = Nothing
    
    If objExcel.ActivePrinter <> saveprinter Then
        'request reset
        ChangePrinterSettings
    End If
    objExcel.Quit
    
    Set objExcel = Nothing

End Sub



Sub ChangePrinterSettings()
' Interactively change your printer settings, including the default
' printer. Click the "Print" button to confirm the new printer settings.
'
' Written by Rob van der Woude
' http://www.robvanderwoude.com

    Dim objPrnDlg, strPrompt, strTitle

    Const vbOK = 1
    Const vbCancel = 2
    Const vbAbort = 3
    Const vbRetry = 4
    Const vbIgnore = 5
    Const vbYes = 6
    Const vbNo = 7

    ' Explain there will be no OK button, the Print button must be
    ' clicked instead.
    strPrompt = "In the next dialog, choose which printer will " _
              & "be the new Default Printer and press the " _
              & """Print"" button to confirm." & vbCrLf & vbCrLf _
              & "Note that any changes you make in the printer " _
              & "settings will be permanent, i.e. they will be " _
              & "the new default settings."
    strTitle = "Choose New Default Printer and/or Printer Settings"
    If MsgBox(strPrompt, vbOKCancel, strTitle) = vbOK Then
        ' Create a dialog object
        Set objPrnDlg = CreateObject("MSComDlg.CommonDialog.1")
        ' Make selections permanent
        objPrnDlg.PrinterDefault = True
        ' Open the Print dialog
        objPrnDlg.ShowPrinter
        

        'GetPrinter = objPrnDlg.DeviceName
        ' Release the object
        Set objPrnDlg = Nothing
    End If
    

End Sub

Open in new window

0
osxmanAuthor Commented:
Rob,

I receive the following error:

Line 31
char 25
error: Invalid character
code:800A0408

Computer is win 7 PRO
0
osxmanAuthor Commented:
BTW I don't care whether I use VBscript or VBA, as long as it does the job.
0
osxmanAuthor Commented:
Ok, it may be my fault here. I opened the excel doc and launched Visual Basic and then realised this excel file contains 15 other worksheets. Only just installed 2007 and it's quite different from 2000!

Anyway, the work sheet I need is called class 5a
0
SiddharthRoutCommented:
osxman: Did you see my post ID: 35225639 ?

Sid
0
osxmanAuthor Commented:
Sid,

yes I tried your post initially and received an error. then used objFile.WriteLine("Hello") and received a different error:

Line 19
char 5
Object doesnt support this property method "writeline"
code:800a01b6
0
SiddharthRoutCommented:
TRIED and TESTED

'~~> Folder where the Excel Files are.
'~~> Also this folder is where the log will be created
Const strSourcePath = "E:\Users\Siddharth Rout\Desktop\EE\"
'~~> Name of the Log File
Const strFile = "Log.txt"

Dim objFSO, objExcel, objWorkbook, objSheet
Dim objFolder, colFiles, objFile,a

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objFolder = objFSO.GetFolder(strSourcePath)
Set colFiles = objFolder.Files
Set a = objFSO.CreateTextFile(strSourcePath & strFile,True)

For Each objFile In colFiles
    Set objWorkbook = objExcel.Workbooks.Open(objFile)
    Set objSheet = objExcel.Workbooks(1).Worksheets(1)
    a.WriteLine("Printed at " & objWorkbook.name)
    '~~> Print to Default Printer
    objSheet.PrintOut
    objWorkbook.Close
Next

a.Close

Set a = Nothing
Set objSheet = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Open in new window

0
SiddharthRoutCommented:
TRIED and TESTED

Ok I have made few other minute changes. Try this

'~~> Folder where the Excel Files are.
Const strSourcePath = "C:\Temp\"
'~~> Path for the log File
Const LogPath = "C:\"
'~~> Name of the Log File
Const strFile = "Log.txt"

Dim objFSO, objExcel, objWorkbook, objSheet
Dim objFolder, colFiles, objFile, a

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application")
Set objFolder = objFSO.GetFolder(strSourcePath)
Set colFiles = objFolder.Files
Set a = objFSO.CreateTextFile(LogPath & strFile,True)

For Each objFile In colFiles
    Set objWorkbook = objExcel.Workbooks.Open(objFile)
    Set objSheet = objExcel.Workbooks(1).Worksheets(1)
    a.WriteLine(objWorkbook.name & " Printed at " & time)
    '~~> Print to Default Printer
    objSheet.PrintOut
    objWorkbook.Close False
Next

a.Close

Set a = Nothing
Set objSheet = Nothing
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing

Open in new window


Sid
0
markdmacCommented:
Similar but another method of doing this:
Dim objFSO, objXL, oFolder, objWorkbook, objSheet, Report, TS
Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objXL = CreateObject("Excel.Application")
'Change the path of where your files are located
Set oFolder = objFSO.GetFolder("C:\Temp\xls")

For Each oFile In oFolder.Files
	If InStr(oFile.Type,"Excel") > 0 Then
		Set objWorkbook = objXL.Workbooks.Open(oFile.Path)
		'Comment out the next line if you don't want to see the 
		'Excel windows open.
		objXL.Application.Visible = True
		'Print to Default Printer
		Set objSheet = objXL.ActiveWorkbook.Worksheets(1)
		objSheet.PrintOut
		objXL.ActiveWorkbook.Close
		Set objSheet = Nothing
		Set objWorkbook = Nothing
		Report = Report & oFile.Name & " was printed at " & Now & vbCrLf
	End If
Next
Set objXL = Nothing
'Change the log file location as needed, by default this will
'create the log file in the same directory as this script
Set TS = objFSO.CreateTextFile ("PrintReport", ForAppending)
TS.Write Report
TS.Close

Open in new window

0
osxmanAuthor Commented:
Sid, it now works but for some reason I don't get a log file. I have checked locations and even scanned my drive for it but to no avail.

Markdmac, your script works also and I get a log file.

This is almost ready! One thing I forgot to ask is, I want to print a particular work sheet in the file. Is this easy to change? Currently it prints worksheet 1 and I want to print worksheet 5

As the script will be lauched by a teacher It would be great if a message displaying "Processing Job, please wait..." could pop up otherwise they will probably keep on launching the script as it does take a while to get through 14 files.

Thanks very much for your expert assistance so far!

Thanks for your assisntance so far.
0
SiddharthRoutCommented:
>>> Sid, it now works but for some reason I don't get a log file.

What have you defined for

Const LogPath = "C:\"

If you haven't changed that then the file will be in C: :)

Sid
0
osxmanAuthor Commented:
I used the following:

Const LogPath = "C:\print"
0
SiddharthRoutCommented:
Change that to

Const LogPath = "C:\print\"

Sid
0
osxmanAuthor Commented:
Sid,

Cool, that did the trick and I get a print out of the log! :O)
0
SiddharthRoutCommented:
Great :)

Sid
0
SiddharthRoutCommented:
So I guess it is sorted?

Sid
0
osxmanAuthor Commented:
Sis,

Yes it is sorted as per my original post.

Is there any chance you could make a small amendment?

 I want to print a particular work sheet in the file. Is this easy to change? Currently it prints worksheet 1 and I want to print worksheet 5

If not, fair enough as it wasnt in my original post and I'll close this question
0
SiddharthRoutCommented:
Change this

Set objSheet = objExcel.Workbooks(1).Worksheets(1)

to

Set objSheet = objExcel.Workbooks(1).Worksheets(5)

Sid
0
osxmanAuthor Commented:
Brillaint!

Thanks again for your persistance and skill!
0
osxmanAuthor Commented:
Top bloke!
0
SiddharthRoutCommented:
You are welcome :)

Sid
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.