Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Printing / September 2006

Tip: Looking for answers? Try searching our database.

Print all excel files from a folder

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
CAM - 28 Sep 2006 05:14 GMT
Can someone point or guide me in how to print all excel files from a folder.
I want to print 2 copies from all Excel files in a particular folder then I
want to move the files to another folder.  Can this be done in a macro and
how?  Any suggestions or website to visit will be appreciated.  Thank you in
advance.
Paul B - 29 Sep 2006 03:50 GMT
CAM, here is some code by Ron de Bruin and one from Tom Ogilvy that together
will do what you want, I think, just put it all in a module and run the
Print_And_Move sub. After you change the paths

Sub Print_And_Move()
PrintFiles
MoveFiles
End Sub

Private Sub PrintFiles()
'will print all excel files in C:\Data
'By: Ron de Bruin
Dim i As Long
   Dim WB As Workbook
   Application.ScreenUpdating = False
   With Application.FileSearch
       .NewSearch
       .LookIn = "C:\Data" '****Change path here*****
       .SearchSubFolders = False
       .FileType = msoFileTypeExcelWorkbooks
       If .Execute() > 0 Then
           For i = 1 To .FoundFiles.Count
            Set WB = Workbooks.Open(.FoundFiles(i))
            WB.PrintOut Copies:=2
            WB.Close False
           Next i
       End If
   End With
   Application.ScreenUpdating = True
End Sub

Private Sub MoveFiles()
'By: Tom Ogilvy
Dim filelist() As Variant
ReDim filelist(1 To 1)
Dim srcPath As String
Dim destPath As String
Dim sFile As String
Dim i As Long
srcPath = "C:\Data\"  '****Change path here*****
destPath = "C:\New Data\"  '****Change path here*****
sFile = Dir(srcPath & "*.xls")
Do While sFile <> ""
filelist(UBound(filelist)) = sFile
ReDim Preserve filelist(1 To UBound(filelist) + 1)
sFile = Dir
Loop
For i = 1 To UBound(filelist) - 1
Name srcPath & filelist(i) As destPath & filelist(i)
Next i
End Sub

Signature

Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

> Can someone point or guide me in how to print all excel files from a folder.
> I want to print 2 copies from all Excel files in a particular folder then I
> want to move the files to another folder.  Can this be done in a macro and
> how?  Any suggestions or website to visit will be appreciated.  Thank you in
> advance.
CAM - 30 Sep 2006 03:11 GMT
Thanks Paul,

I will give it a shot.

Regards,

> CAM, here is some code by Ron de Bruin and one from Tom Ogilvy that
> together
[quoted text clipped - 58 lines]
> in
>> advance.

Rate this thread:






 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.