Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Printing a Sheet without opening workbook
Thanks I will give it a try in the am :) *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
only the last sheet tab appears when opening workbook | Excel Discussion (Misc queries) | |||
Opening workbook at sheet required. | New Users to Excel | |||
Opening a workbook to a chosen sheet? | Excel Discussion (Misc queries) | |||
Hyperlinks and opening any sheet a workbook | Excel Programming | |||
Opening a workbook at a specific sheet | Excel Programming |