Printing a Sheet without opening workbook
 

Hi there

Is it possible to writeVBA that will enable me to print from ALL Excel
Workbooks within a specified folder a particluar sheet? (sheet B) and
lets assume folder structure is //results/day1

(Problem is workbooks are all named differently, but all contain same
sheet name)

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Printing a Sheet without opening workbook
 

Hi

This example will print the first sheet of the all workbooks in the folder

Change MyPath

Sub Test()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook

'Fill in the path\folder where the files are
MyPath = "C:\Data" 'or "\\Username\SharedDocs"
'Add a slash at the end if the user forget
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).PrintOut

mybook.Close savechanges:=True
Next Fnum

CleanUp:
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Darin Kramer" wrote in message ...
Hi there

Is it possible to writeVBA that will enable me to print from ALL Excel
Workbooks within a specified folder a particluar sheet? (sheet B) and
lets assume folder structure is //results/day1

(Problem is workbooks are all named differently, but all contain same
sheet name)

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Printing a Sheet without opening workbook
 

Mmmm

Change
mybook.Close savechanges:=True
to
mybook.Close savechanges:=False

No need to save


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
Hi

This example will print the first sheet of the all workbooks in the folder

Change MyPath

Sub Test()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim mybook As Workbook

'Fill in the path\folder where the files are
MyPath = "C:\Data" 'or "\\Username\SharedDocs"
'Add a slash at the end if the user forget
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
mybook.Worksheets(1).PrintOut

mybook.Close savechanges:=True
Next Fnum

CleanUp:
Application.ScreenUpdating = True
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Darin Kramer" wrote in message ...
Hi there

Is it possible to writeVBA that will enable me to print from ALL Excel
Workbooks within a specified folder a particluar sheet? (sheet B) and
lets assume folder structure is //results/day1

(Problem is workbooks are all named differently, but all contain same
sheet name)

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***

Printing a Sheet without opening workbook
 

Thanks Ron, - Can i not specify the name of a sheet to be printed as
opposed to the first sheet in each book ?(Users may have moved the order
of the sheets around a bit)

Regards

D


*** Sent via Developersdex http://www.developersdex.com ***

Printing a Sheet without opening workbook
 

Sure

Change the 1 to "yoursheetname" (with the "" )

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Darin Kramer" wrote in message ...

Thanks Ron, - Can i not specify the name of a sheet to be printed as
opposed to the first sheet in each book ?(Users may have moved the order
of the sheets around a bit)

Regards

D


*** Sent via Developersdex http://www.developersdex.com ***

Printing a Sheet without opening workbook
 

Thanks I will give it a try in the am :)


*** Sent via Developersdex http://www.developersdex.com ***