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