Print worksheets in external workbooks
I have written the following PrintFiles() procedure to print 3 worksheets
from an external workbook. The code will look in column A for a list of workbook files. There will be many workbook files (50+) with the same worksheets to print. If it finds the file, then it opens the workbook and prints the worksheets. The code seems to work when I'm in debug mode, but not when I run it without the debugger. It opens the workbook file and then stops. Any suggestions? Public Sub PrintFiles() ' ' PrintFiles Macro ' Print files listed in Files tab ' ' Keyboard Shortcut: Ctrl+Shift+P ' '--Updated: 2008-Aug-29 Dim intRow As Integer Dim strFile As String Range("A1").Select intRow = 1 strFile = ActiveCell.Value Do Until strFile = "" '--Check for existence of file If Dir(strFile) < "" Then '--open workbook file Workbooks.Open Filename:=strFile '--print worksheet 1 ActiveWorkbook.Sheets("13 months").Select ActiveWorkbook.ActiveSheet.PrintOut Copies:=1 '--print worksheet 2 ActiveWorkbook.Sheets("comp op stmt").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--print worksheet 3 ActiveWorkbook.Sheets("act v bud").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--close workbook ActiveWorkbook.Close False End If ActiveCell.Offset(intRow, 0).Activate strFile = ActiveCell.Value Loop Exit Sub End Sub |
Print worksheets in external workbooks
Drop the Shift from the shortcut key combination and try it again.
TJ Dowling wrote: I have written the following PrintFiles() procedure to print 3 worksheets from an external workbook. The code will look in column A for a list of workbook files. There will be many workbook files (50+) with the same worksheets to print. If it finds the file, then it opens the workbook and prints the worksheets. The code seems to work when I'm in debug mode, but not when I run it without the debugger. It opens the workbook file and then stops. Any suggestions? Public Sub PrintFiles() ' ' PrintFiles Macro ' Print files listed in Files tab ' ' Keyboard Shortcut: Ctrl+Shift+P ' '--Updated: 2008-Aug-29 Dim intRow As Integer Dim strFile As String Range("A1").Select intRow = 1 strFile = ActiveCell.Value Do Until strFile = "" '--Check for existence of file If Dir(strFile) < "" Then '--open workbook file Workbooks.Open Filename:=strFile '--print worksheet 1 ActiveWorkbook.Sheets("13 months").Select ActiveWorkbook.ActiveSheet.PrintOut Copies:=1 '--print worksheet 2 ActiveWorkbook.Sheets("comp op stmt").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--print worksheet 3 ActiveWorkbook.Sheets("act v bud").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--close workbook ActiveWorkbook.Close False End If ActiveCell.Offset(intRow, 0).Activate strFile = ActiveCell.Value Loop Exit Sub End Sub -- Dave Peterson |
Print worksheets in external workbooks
That worked! Thanks a bunch.
TJ "Dave Peterson" wrote: Drop the Shift from the shortcut key combination and try it again. TJ Dowling wrote: I have written the following PrintFiles() procedure to print 3 worksheets from an external workbook. The code will look in column A for a list of workbook files. There will be many workbook files (50+) with the same worksheets to print. If it finds the file, then it opens the workbook and prints the worksheets. The code seems to work when I'm in debug mode, but not when I run it without the debugger. It opens the workbook file and then stops. Any suggestions? Public Sub PrintFiles() ' ' PrintFiles Macro ' Print files listed in Files tab ' ' Keyboard Shortcut: Ctrl+Shift+P ' '--Updated: 2008-Aug-29 Dim intRow As Integer Dim strFile As String Range("A1").Select intRow = 1 strFile = ActiveCell.Value Do Until strFile = "" '--Check for existence of file If Dir(strFile) < "" Then '--open workbook file Workbooks.Open Filename:=strFile '--print worksheet 1 ActiveWorkbook.Sheets("13 months").Select ActiveWorkbook.ActiveSheet.PrintOut Copies:=1 '--print worksheet 2 ActiveWorkbook.Sheets("comp op stmt").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--print worksheet 3 ActiveWorkbook.Sheets("act v bud").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1 '--close workbook ActiveWorkbook.Close False End If ActiveCell.Offset(intRow, 0).Activate strFile = ActiveCell.Value Loop Exit Sub End Sub -- Dave Peterson |
All times are GMT +1. The time now is 12:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com