VBScripting for Ms Excel - AutoPrint a file

bujumah
bujumah used Ask the Experts™
on
Hi,
This is simple but I could not do it myself.

An excel file is generated every night in a network directory @ 0030 and given a name (file-date-month_year.xls).
I need to copy and print that file automatically every day but could not find the code for wildcard or to select, open and print the latest file from the file list.

The code I'm using is:

 
'1) Copy the file from original location to print folder @ 00:35
Const OverwriteExisting = True
Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFile "\\location\file-03-07_2010.xls" , "C:\print\", OverwriteExisting


'2) Open and print the file C:\print\file-03-07_2010.xls @ 00:40
Dim objExcel, objWorkbook, strFile

' Connect to Excel
Set objExcel = CreateObject("Excel.Application")

' *******************************************
' Put the path and file name to print Here
' *******************************************
strFile  = "C:\print\file-03-07_2010.xls"

' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
    (strFile)

' Print to Default Printer
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.PrintOut

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

Open in new window


Note: after searching in Google and EXP-EX I collected this code.

Would anybody help me of getting this sorted out or advice of a better way to do it please?

Thanks
Mohamed
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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

Open in new window

Author

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

Open in new window

Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

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

Author

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

Open in new window

Author

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

Author

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

Author

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

Open in new window

Author

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

Open in new window

Author

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

Open 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

Author

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


Thanks a lot dear.

Author

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

Open in new window

Image2.jpg
Hi,

you wrote in your initial post:

file-date-month_year.xls

So you need a file like

NameOfTheFile-20-07_2010.xls

Your file is missing at least the "-" at the beginning of the name.

Cheers,

Christian

Author

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

Author

Commented:
This is working fine

Thanks a lot dear

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial