Printing a Sheet without opening workbook

[Darin Kramer]

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 ***

[Ron de Bruin]

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" <darin_kramer@hotmail.com> wrote in message news:OQsURSLzFHA.2152@TK2MSFTNGP10.phx.gbl...
> 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 ***

[Ron de Bruin]

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" <rondebruin@kabelfoon.nl> wrote in message news:evX9nhLzFHA.2932@TK2MSFTNGP10.phx.gbl...
> 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" <darin_kramer@hotmail.com> wrote in message news:OQsURSLzFHA.2152@TK2MSFTNGP10.phx.gbl...
>> 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 ***
>
>

[Darin Kramer]

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 ***

[Ron de Bruin]

Sure

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

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


"Darin Kramer" <darin_kramer@hotmail.com> wrote in message news:%23rNES$OzFHA.664@tk2msftngp13.phx.gbl...
>
> 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 ***

[Darin Kramer]

Thanks I will give it a try in the am


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