Print all files in folder with macro

IsaacAsher

New Member
Joined
Jul 25, 2007
Messages
2
Greetings!

I found the following code on this same board and used it successfully a few times. I made a few edits to the original code as I just need to print the entire file as opposed to printing individual sheets as the code was first written.

My goal is to loop through all files in a the current folder, and print them if they are not called "REQUEST-PRINTER.xls" which is the name of the file where I placed this code.

Code:
Public Count
Sub Printer()
   Dim fso As Object, oFolder As Object, oFile As Object
   Dim path_name As String
   
   path_name = ActiveWorkbook.Path
   Application.DisplayAlerts = False
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set oFolder = fso.GetFolder(path_name)

   For Each oFile In oFolder.Files
      If Left(oFile.Name, 7) <> "REQUEST" Then
         Workbooks.Open oFile.Name
         For x = 1 To Count
            ActiveSheet.PrintOut
         Next x
         ActiveWorkbook.Close
      End If
      
   Next
End Sub

As I said, this worked the first few times (after my edits, mind you). Today when I tried to run it, it said:

Code:
Run-time error '1004':

'85013615.xls' could not be found.  Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files on the File menu, make sure that the file has not been renamed, moved, or deleted.

The error is occurring on Workbooks.Open oFile.Name

Not sure what happened here, as 85013615.xls is indeed the correct name of the first file that should be printed, and I did not make any edits to the code after my initial re-working.

Any ideas?

Most thankful,

Isaac
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,357
Office Version
  1. 365
Platform
  1. Windows
Isaac

Are you sure you are using the full path when trying to open the file?

It looks to me like you are only using the filename.
 

IsaacAsher

New Member
Joined
Jul 25, 2007
Messages
2
Full path vs. file name

I found the problem!!

It was the object variables. I noticed when I discovered I could not run the program two times in a row.

I added the following two lines to the end of the program:

Code:
   Set oFolder = Nothing
   Set fso = Nothing

Now the world is once again right! :)
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top