Results 1 to 3 of 3

Files automatically open when printed to file

Threaded View

bhastings Files automatically open when... 10-20-2010, 06:32 AM
TMS Re: Files automatically open... 10-20-2010, 09:02 AM
bhastings Re: Files automatically open... 10-20-2010, 10:26 AM
  1. #1
    Registered User
    Join Date
    10-20-2010
    Location
    Norwich, England
    MS-Off Ver
    Excel 2003
    Posts
    6

    Files automatically open when printed to file

    Good Morning,

    I'm brand new here and also brand new to vba as well so please forgive me if my code is pretty poor

    I have managed to cobble together a macro which will go through every worksheet in an Excel 2003 workbook and print them all to a file with a unique filename. This works perfectly for me apart from one little thing.

    I want to stop it from automatically opening each file after it is saved. Currently what happens is that Windows Picture and Fax Viewer will open for each file after it is saved to the specified location. Thankfully it doesn't open up a new instance of the program for each sheet but it still looks a bit messy and I would love to be able to stop it from happening.
    I'm not sure it is even possible to be honest but I thought I would register on here and get some experts views on this matter.

    Please see my current code below:-

    Sub PrintSheets()
    
    Dim Current As Worksheet
    
    For Each Current In Worksheets
    
         'Ignores the first Blank Sheet and any hidden sheets
         If Current.Name <> "Blank" And Current.Visible = xlSheetVisible Then 
    
              Application.ActivePrinter = "Microsoft Office Document Image Writer on Ne01:"
              Current.PrintOut Copies:=1, PrintToFile:=True, Collate _
                       :=True, PrToFileName:="C:\" & Current.Name & ".tif"
    
         End If
    
    Next
    
    End Sub
    If any of this doesn't make sense or if you can offer suggestions which would further optimise my code then that would be fantastic.

    Thanks,

    Ben
    Last edited by bhastings; 10-20-2010 at 10:27 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1