Print the same page from many different named files

[Guest]

Dear all

I have a folder which includes 200 files of excel with exactly the same
format.
There is any possibility to make a macro in order to get into the folder and
print a specific sheet?
I.e Form file September print form all excel files the sheet named "IS"

Because it is quite difficult to open one by one the files and print one
sheet from each file. most times i am missing files or sheets.

Any help on that?

 

[Jim Rech]

Something like:

Sub a()
Dim FilePath As String
Dim FName As String
On Error Resume Next ''in case sheet does not exist
FilePath = "c:\Files\"
FName = Dir(FilePath & "*.xls")
While FName <> ""
Workbooks.Open FilePath & FName
Worksheets("IS").PrintOut
ActiveWorkbook.Close False
FName = Dir()
Wend
End Sub


--
Jim Rech
Excel MVP
| Dear all
|
| I have a folder which includes 200 files of excel with exactly the same
| format.
| There is any possibility to make a macro in order to get into the folder
and
| print a specific sheet?
| I.e Form file September print form all excel files the sheet named "IS"
|
| Because it is quite difficult to open one by one the files and print one
| sheet from each file. most times i am missing files or sheets.
|
| Any help on that?

 

[Guest]

Hello Mr. Rech

Thank you very much for your help but i need a little more help.
Where i input the name of the sheet which i want to print?
And where i am running the code?
Soeey but my visual basic knowledge is not very good.

Thanks in advance manos

 

[Guest]

Hi again
another question. How or where i am going to run this code?
I have to do an add inn?

Regards Manos

 

[Dave Peterson]

This is the line that prints the worksheet:

Worksheets("IS").PrintOut

So change "IS" to whatever you need.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel and test it out via:
tools|macro|macros...