Print multiple excel files using VBscript

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"

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

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

Select allOpen in new window

Rob,

I receive the following error:

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

Computer is win 7 PRO

BTW I don't care whether I use VBscript or VBA, as long as it does the job.

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

osxman: Did you see my post ID: 35225639 ?

Sid

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

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

Select allOpen in new window

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

Select allOpen in new window

Sid

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.

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

I used the following:

Const LogPath = "C:\print"

Change that to

Const LogPath = "C:\print\"

Sid

Sid,

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

Great :)

Sid

So I guess it is sorted?

Sid

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

Change this

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

to

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

Sid

Brillaint!

Thanks again for your persistance and skill!

Top bloke!

You are welcome :)

Sid