Macro to Print All Files in a Folder

[Jim May]

Off google I found the following code, which I have since modified to
accomplish my subject objective; <<to no avail>> can someone assist?

Sub Tester9()
PathOnlysource = "C:\Formulas\Backuptest"
ChDir PathOnlysource
TheFile = Dir(PathOnlysource & "\*.xls")
Do While TheFile <> ""
ActiveSheet.PrintOut
TheFile = Dir
Loop
End Sub

 

[Rollin_Again]

You need to open each of the workbooks in the loop before trying t
print them out.


PATHONLYSOURCE = \"C:\TEST\"
CHDIR PATHONLYSOURCE
THEFILE = DIR(PATHONLYSOURCE & \"\*.XLS\")
DO WHILE THEFILE <> \"\"
WORKBOOKS.OPEN FILENAME:=PATHONLYSOURCE & \"\\" & THEFILE
ACTIVESHEET.PRINTOUT
WORKBOOKS(THEFILE).CLOSE
THEFILE = DIR
LOOP



Rolli

 

[Bob Phillips]

Jim,

What is that you want that you don't get? It may seem obvious, but I tried
this with my directory and it worked fine for me.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

did you add code to open the workbook, or just get "n" copies of the sheet
that was active when you ran the code?

 

[Jim May]

Tom:
Thanks; No, I failed to "open" the workbooks. I've since
gotten my answer from code written by Ron de Bruin;
Have a good one..

 

[Jim May]

Bob: Thanks for looking in on this..
What I was trying to do << and have since accomplished-below>> was:
using what i call a surrogate workbook to run a macro which opens
each .xls file in a folder and prints each and then closes each. The code
I posted was printing my surrogate activesheet twice since my test folder had two files in it..
I later found this code by Ron de Bruin off google.. [works perfectly]

Sub PrintAllinFolder()
Dim i As Long
Dim WB As Workbook
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\WINDOWS\Desktop\Temp Excel Formulas\Backuptest"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set WB = Workbooks.Open(.FoundFiles(i))
WB.PrintOut
WB.Close False
Next i
End If
End With
Application.ScreenUpdating = True
End Sub