VBScripting for Ms Excel - AutoPrint a file

Hi,

you could find the right files with the attached code and use a sub "PrintExcelFile" which accepts the path and file as a single string which uses your code to print it.

But you should move the creating of the Excel object into this code or a calling code, otherwise a new Excel instance will be created for every print which is not needed (of course the Quit command must also be outside the PrintExcelFile).

Cheers,

Christian

Public Sub PrintFiles()
   Dim strFile As String
   Const strPath = "C:\print\"
   
   strFile = Dir(strPath & "*.xls")
   
   Do While strFile <> ""
      If InStr(strFile, "-" & Day(VBA.Date) & "-" & Month(VBA.Date) & "_" & Year(VBA.Date)) > 0 Then
         PrintExcelFile strPath & strFile
      End If
      strFile = Dir
   Loop
End Sub

Select allOpen in new window

Thanks for your reply.

Excel file creation is done sepritly by another code/server.

What I need is:
1. Get the name of the latest excel file in that folder.
2. Put than name for a printing.

Anybody could modify/post my code fully to be easy.


Thanks
Mohamed

This one should work.

Public Sub PrintFiles()
   Dim strFile As String
   Dim objFSO As Object
   Dim objExcel As Object
   Dim objWorkbook As Object
   Dim objSheet As Object
   
   Const strPath = "C:\print\"
   Const OverwriteExisting = True
   
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   ' Connect to Excel
   Set objExcel = CreateObject("Excel.Application")

   strFile = Dir(strPath & "*.xls")
   Do While strFile <> ""
      If InStr(strFile, "-" & Day(VBA.Date) & "-" & Month(VBA.Date) & "_" & Year(VBA.Date)) > 0 Then
         objFSO.CopyFile "\\location\" & strFile, "C:\print\", OverwriteExisting
         Set objWorkbook = objExcel.Workbooks.Open(strPath & strFile)
         Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
         objSheet.PrintOut
         objExcel.ActiveWorkbook.Close
      End If
      strFile = Dir
   Loop
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing
   Set objFSO = Nothing
End Sub

Select allOpen in new window

Thanks for the effort you made.

I saved the file into my c:\shouldwork.vbs

I got this error.



error.jpg

Hi,

sorry, my fault, I didn't realized that you meant VBScript - my code above should work if you run it from VBA because there is a Dir command. VBScript doesn't support a Dir command, you need additional functions to write one like explained here:

http://www.source-code.biz/snippets/vbscript/1.htm

But normally it is no need to use VBScript which makes things more complicate - because you need in any case Excel installed to open a file and print it, so you can also use VBA in Excel to run this.

I created this workbook which does the job. You can change the source and target folder by simply changing the values in D9 and D10 and click "Print".

Cheers,

Christian

PrintFiles.xls

Thanks Christian,
I preffer to use vbs as I have 90% of the code ready.

This is the shortest possible code which should do the job in VBScript.

By the way: You can also use the Excel sheet above in a batch if you add this to the module:

Public Sub Auto_Open()
   PrintFiles
End Sub

and then use a batch which opens Excel with this file. If it is Excel 2007 you need to add the location where you saved this into the trusted folder list to get the macros executed.

Cheers,

Christian

Const strSourcePath = "\\location\folder"
Dim objFSO
Dim objExcel
Dim objWorkbook
Dim objSheet
Dim objFolder
Dim colFiles
Dim objFile

   Set objExcel = CreateObject("Excel.Application")
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strSourcePath)
   Set colFiles = objFolder.Files
   For Each objFile in colFiles
      If InStr(objFile.Name, "-" & Right("00" & Day(Date), 2) & "-" & _
                                   Right("00" & Month(Date), 2) & "_" & Year(Date)) > 0 Then
         ' Open the Excel File for Printing
         Set objWorkbook = objExcel.Workbooks.Open(objFile)
         ' Print to Default Printer
         Set objSheet = objExcel.Workbooks(1).Worksheets(1)
         objSheet.PrintOut
         objWorkbook.Close
      End If
   Next
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing

Select allOpen in new window

Hi,
Sorry for late. I was off.
It is still giving me Error in line 13 when I execute the file.vbs

Hi,

I don't know why - when I run this it works without problems. Did you change the path at the beginning of the script?

Cheers,

Christian

same
Image1.jpg

Hi,

if you compare it is not the same as above. The error message clearly say what's the problem: "Path not found". You need to supply a path in the variable in the first line which exists and can be accessed by VBScript in your system.

Cheers,

Christian

Dear,
Now I've corrected the path.
Excuted the file.
No errors :)
But nothing printed :)

Const strSourcePath = "c:\print"
Dim objFSO
Dim objExcel
Dim objWorkbook
Dim objSheet
Dim objFolder
Dim colFiles
Dim objFile

   Set objExcel = CreateObject("Excel.Application")
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strSourcePath)
   Set colFiles = objFolder.Files
   For Each objFile in colFiles
      If InStr(objFile.Name, "-" & Right("00" & Day(Date), 2) & "-" & _
                                   Right("00" & Month(Date), 2) & "_" & Year(Date)) > 0 Then
         ' Open the Excel File for Printing
         Set objWorkbook = objExcel.Workbooks.Open(objFile)
         ' Print to Default Printer
         Set objSheet = objExcel.Workbooks(1).Worksheets(1)
         objSheet.PrintOut
         objWorkbook.Close
      End If
   Next
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing

Select allOpen in new window

I've also tried

Const strSourcePath = "c:\print\"
Dim objFSO
Dim objExcel
Dim objWorkbook
Dim objSheet
Dim objFolder
Dim colFiles
Dim objFile

   Set objExcel = CreateObject("Excel.Application")
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strSourcePath)
   Set colFiles = objFolder.Files
   For Each objFile in colFiles
      If InStr(objFile.Name, "-" & Right("00" & Day(Date), 2) & "-" & _
                                   Right("00" & Month(Date), 2) & "_" & Year(Date)) > 0 Then
         ' Open the Excel File for Printing
         Set objWorkbook = objExcel.Workbooks.Open(objFile)
         ' Print to Default Printer
         Set objSheet = objExcel.Workbooks(1).Worksheets(1)
         objSheet.PrintOut
         objWorkbook.Close
      End If
   Next
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing

Select allOpen in new window

And

Const strSourcePath = "c:\print\*.xls"
Dim objFSO
Dim objExcel
Dim objWorkbook
Dim objSheet
Dim objFolder
Dim colFiles
Dim objFile

   Set objExcel = CreateObject("Excel.Application")
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strSourcePath)
   Set colFiles = objFolder.Files
   For Each objFile in colFiles
      If InStr(objFile.Name, "-" & Right("00" & Day(Date), 2) & "-" & _
                                   Right("00" & Month(Date), 2) & "_" & Year(Date)) > 0 Then
         ' Open the Excel File for Printing
         Set objWorkbook = objExcel.Workbooks.Open(objFile)
         ' Print to Default Printer
         Set objSheet = objExcel.Workbooks(1).Worksheets(1)
         objSheet.PrintOut
         objWorkbook.Close
      End If
   Next
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing

Select allOpen in new window

Hi,

the first variant is OK if there is no error.

The rest depends on the files in your folder, it will only print if the filename contains "-19-07_2010" as you stated in your initial post (to be exact, "19-07_2010" must be the current date, any older file will not be printed, also any newer file). Any other xls file will be ignored. And of course: The folder should not contain any other file than Excel files, otherwise you will get an error in line 18 because it doesn't test for Excel file extension.

Cheers,

Christian

Aha,
I will give it a try based on your advice and let you know the result.


Thanks a lot dear.

I gave it a try and nothing was printed :(

Code and path attached

Const strSourcePath = "c:\print\"
Dim objFSO
Dim objExcel
Dim objWorkbook
Dim objSheet
Dim objFolder
Dim colFiles
Dim objFile

   Set objExcel = CreateObject("Excel.Application")
   Set objFSO = CreateObject("Scripting.FileSystemObject")
   Set objFolder = objFSO.GetFolder(strSourcePath)
   Set colFiles = objFolder.Files
   For Each objFile in colFiles
      If InStr(objFile.Name, "-" & Right("00" & Day(Date), 2) & "-" & _
                                   Right("00" & Month(Date), 2) & "_" & Year(Date)) > 0 Then
         ' Open the Excel File for Printing
         Set objWorkbook = objExcel.Workbooks.Open(objFile)
         ' Print to Default Printer
         Set objSheet = objExcel.Workbooks(1).Worksheets(1)
         objSheet.PrintOut
         objWorkbook.Close
      End If
   Next
   Set objSheet = Nothing
   Set objWorkbook = Nothing
   objExcel.Application.Quit
   Set objExcel = Nothing

Select allOpen in new window

Image2.jpg

I will try it once get back to work on 29th

This is working fine

Thanks a lot dear