Print the same sheet from different file

[Guest]

Hello every body.

I would like to ask if you have ever had a problem like this.

I have a folder which involve 200 sales reports, which all of them having
exactly the same format. The problem is that i want to get into the file and
give a print for sheet 2 from all files. I do not want to open it one by one.
it is to much 200 files and always miss, one.
There is any possibility to have a visual basic code to make this job? and
how can i work with it? I have seen something on the internet but i don not
how it works?
There is also the possibilit to create an add inn on the toolbars and give
from there the print command?

I am not so strong on visual basic and for that reason i would like as much
description of a solution, as it is possible.

Thanks in advance Manos

 

[Ron de Bruin]

Hi Manos

Try this for all files in the folder C:\Data
It will print the second sheet of each file.

Copy the code in a normal module in a workbook that is
not in the folder C:\Data.

Open a new workbook
Alt-F11
Insert>Module from the menu bar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 you get a list of your macro's
Select "TestFile1" and press Run




Sub TestFile1()
Dim basebook As Workbook
Dim mybook As Workbook
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String

SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Do While FNames <> ""
Set mybook = Workbooks.Open(FNames)
mybook.Sheets(2).PrintOut
mybook.Close False
FNames = Dir()
Loop
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub