How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Michael Hannigan Your Own Question
Michael Hannigan
Michael Hannigan, Office Specialist
Category: Microsoft Office
Satisfied Customers: 11819
Experience:  25+ Years Experience in Computers and Electronics. Microsoft Certified MCSE, MCDBA, MCP, Microsoft Partner
Type Your Microsoft Office Question Here...
Michael Hannigan is online now
A new question is answered every 9 seconds

I need a macro that will search this folder D:\new sws folder\maintena

This answer was rated:

I need a macro that will search this folder D:\new sws folder\maintenance\poreq for all excel files that have a date that I input all the file names are XXXXX XXXXX PO-otp-5-6-13, the 5-6-13 is the date I need it looked up by then when all the files have been found I need it to print them and close the files
Can this be done
Help I have no idea where to start
Hello. My name is Michael. I can help you wiht your question.

Ideally, would this be a macro that woudl run from Excel, or would you be developing it to run outside of Excel?

Customer: replied 4 years ago.

from inside excel off of a button on the form

Ok, Ken. I'm going to work on this for you. Ideally, I can provide you with some simple code you can use.


Ken, I have the basic macro for you. Currently, it will take your input from the input box and search the path for that string and print those that match.

Sub Batch_Print()
Dim Input_Dir, Print_File As String
Input_Dir = InputBox _
("Input directory path containing the files to print")
' Defines Print_File equal to the first Microsoft Excel file found
' in the directory specified in the InputBox above.
Print_File = Dir("D:\new sws folder\maintenance\poreq\*"&Input_Dir & "*.xl*")

' If you are using Microsoft Excel for the Macintosh, you need
' to change Print_File = Dir(Input_Dir & "' .xl*") to
' If you are using Microsoft Excel 5.0, use this line
' Print_File = Dir(Input_Dir & ":", MacID("XLS5"))
' If you are using Microsoft Excel 98, use this line
' Print_File = Dir(Input_Dir & ":", MacID("XLS8"))
' and change Workbooks.Open Filename:=Input_Dir & "\" & Print_File
' to:
' Workbooks.Open Filename:=Input_Dir & ":" & Print_File

' Loops through the directory specified in the above InputBox
' and opens each workbook in the directory, prints all sheets
' in the workbook and closes the workbook. Continues until are
' all files are printed.
Do While Len(Print_File) > 0
Workbooks.Open Filename:=Print_File
ActiveWorkbook.PrintOut Copies:=1
Print_File = Dir()
End Sub

Customer: replied 4 years ago.

Its giving me a error message it says


Run-time error '1004':

'PO-kenm-3170-5-5-13 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, make sure that the file has not been renamed, moved or deleted


I verified the file name and location and even opened it manualy

Customer: replied 4 years ago.

I am using windows 7, office 2010

Ken... just need a few minutes to look at the problem.

Ken, it looks like it finds the files, but then when it tries to print, I didn't have the path in the file name. So change
Workbooks.Open Filename:=Print_File
Workbooks.Open Filename:="D:\new sws folder\maintenance\poreq\"&Print_File

This should provide the basic functionality you are looking for.

Michael Hannigan and 6 other Microsoft Office Specialists are ready to help you
Customer: replied 4 years ago.

Thanks a lot that seems to be doing it

Customer: replied 4 years ago.

My question page says im still waiting for a response from the pro did I not close this out correctly you did a awesome job and would like to make sure I did what I needed to on my end for you

You did, Ken. Thank you very much! And thank you for the compliment!


It was a pleasure working with you!