Solved: printing multiple workbooks from a check list

[mperrah]

I have 50 workbooks with one page each. One cell has a date field.
I open windows explorer and control click all the files I want printed then right click and chose print. I print the pages a day ahead so
I use today() + "1" in a cell for the date to show tomorrows date when I print. When each file prints Excel asks to save before closing.
I want to make a check box on one page with all the file names and make a macro or script to print the checked files and close with out saving.

[austenr]

See if this link helps you. It would need to be modified to do what you ask but it is a starting point.

http://j-walk.com/ss/excel/tips/tip48.htm

[lilstevie]

This looks promising:

http://www.exceltip.com/st/Print_all...Excel/455.html

[mdmackillop]

Two subs
First will list files from a named folder
[VBA]
Option Explicit
Sub ListFiles()
Dim i As Long
Dim Path As String
Path = "C:\AAA\"
With Application.FileSearch
.LookIn = Path
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute
For i = 1 To .FoundFiles.Count
Range("A" & i + 2) = .FoundFiles(i)
Next i
End With
Columns.AutoFit
End Sub
[/VBA]
Second will open any file with an x (or anything) in column B, add date to specified cell, print it and close without saving
[VBA]


Sub PrintFiles()
Dim cel As Range
Dim wb As Workbook
For Each cel In Range(Cells(3, 1), Cells(Rows.Count, 1).End(xlUp))
If cel.Offset(, 1) <> "" Then
Set wb = Workbooks.Open(cel)
wb.Sheets(1).Range("A1") = Now() + 1
wb.Sheets(1).PrintOut
wb.Close False
End If
Next
End Sub

[/VBA]

[mperrah]

The check box sounds just right.
I'm trying it tonight.
Thank you for the help.
Mark

[mperrah]

thank you, austenr
This script works awesome.
I had 50 workbooks that i had to hand pick then right click to print and close a save dialoge for each workbook.
This script works for worksheets so I copied the sheet from each of the workbooks into one workbook and labeled the tabs.
The script makes a check box to print each tab.
Since all the worksheets are still open the save dialoge only comes up when I close the whole workbook.
This is awesome.
Thank you.
Mark

[mperrah]

The print dialog script is working great but I've added to the number of worksheets. I have the worksheet names showing up in a dialoge to select which to print, but I have over 50 worksheets now and the dialoge is too tall for the screen and i cant scroll

is there a way to either scroll in a dialog window, or have the results wrap to 2 or 3 columns in a sized window,

the tab labels are first and last name and a 4 digit number but there are at least fifty, 48 fit on the screen in one column.

Thanks again for the coding, my co-workers are stoked

[austenr]

Glad to help. It's late on the east coast but i will give it a look tomorrow.

[lucas]

You might take a look at this userform alternative...
listbox will scroll for you. Click on additional sheets to print multiple sheets at one time.

you will have to change .printpreview to .print after you have tested it.

[austenr]

Thanks Steve. Didn't have time to do that for him today.

[lucas]

No problem, Hope I'm not stepping on toes....just an alt idea

[austenr]

MP - Can you post the code you have?

[mperrah]

option explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.CountA(CurrentSheet.Cells) <> 0 And _
CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select Techs"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select Replace:=False
End If
Next cb
ActiveWindow.SelectedSheets.PrintOut copies:=1
' ActiveSheet.PrintPreview 'for debugging
ActiveSheet.Select
End If
Else
MsgBox "All worksheets are empty."
End If

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Activate



End Sub

[mperrah]

lucas,
your form works great for my sizing issues, but can the worksheets get sent as one print job? is that what you meant by changing .printpreview to .print?
Mark

[lucas]

yes, if you change this line in the command button 1 click procudure:
[VBA]ActiveWorkbook.Sheets(shtname).PrintPreview
[/VBA]
to this:
[VBA]ActiveWorkbook.Sheets(shtname).PrintOut[/VBA]
then it will just print the sheets you select with no preview....