Looking for VBS to print selected worksheets

Microsoft Microsoft office professional...
December 10, 2009 at 20:59:48
Specs: Windows XP, 1024
I am looking for a vb script that is able to print selected worksheet in command line. I do not want to print the whole workbook, only selected ones.

Please help.

See More: Looking for VBS to print selected worksheets

Report •

December 11, 2009 at 09:24:20
You could attach this to a button...

Sub PrintSelectedSheets()
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Report •

December 11, 2009 at 11:32:09
Thank you very much!

How do you print in command line? Can you give me an example?

Report •

December 11, 2009 at 12:55:57
Not quite sure what you mean by "command line".

To me "command line" means a DOS command entered via Start...Run.

I'll assume you mean "How do I put the macro into a workbook and assign it to a button?"

I can't take responsibility for anything that happens to your workbook, so I suggest you make a back up copy just to be safe.

1 - Open your workbook and press Alt-F11 to open the VBA Editor.
2 - Choose Insert...Module
3 - Paste the code I offered into the window that opens.
4 - Close the VBA editor (save your workbook, if desired)
5 - Back in the workbook itself, choose Tools...Customize...Commands tab
6 - Scroll down to Macros and choose either the Custom Menu Item or Custom Button
7 - Drag the Button to a toolbar or the Menu Item to a Drop Down menu
8 - With the Customize Dialog Box still open, Right Click the item you added and choose Assign macro.
9 - Choose the Macro from the list and double click it.
10 - You can the edit button image and/or text at this point (as long as the Customize Dialog Box is open)
11 - Close the Customize Dialog Box and save your workbook.

Clicking the Macro button or menu item should print out the selected sheets.

Report •

Related Solutions

December 11, 2009 at 16:30:48
Yes, command line for me is Dos.

I want to do something like this.
c:> C:\print.vbs C:\book.xls
and then you can select the sheet by using switch behind the command line.

In addition, the workbook is automatic generated every month from the server. So if I have to plugin the macro into the workbook that will be very inconvenience.

So it is best for me to write a batch with vbs to print the routine reports in Dos command line.

Report •

December 11, 2009 at 20:38:48
I have never heard of using command line switches to select specific sheets in a workbook for printing. I'm not saying it can't be done, but I don't know how you would pass the variable (the switch) to the application from DOS.

As a possible workaround, if you put the code below into the ThisWorkbook module of a different workbook, it would to tell Excel to print specific sheets from another workbook.

For example, if you put this code in the ThisWorkbook module of C:\MyPrintCode.xls, and then entered C:\MyPrintCode.xls as a command line entry, it would print pages 1 and 3 of C:\TheBookToPrint.xls. That way you wouldn't have to put any code into the workbook that the server generates.

In this case, the workbook name and sheets to print are hardcoded, but the code could present dialog boxes and get that information from the user each time.

Private Sub Workbook_Open()
     Application.Visible = False
     Workbooks.Open Filename:="C:\TheBookToPrint.xls"
     ActiveWorkbook.Sheets(Array("Sheet1", "Sheet3")).Select
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Note: If you want to open the workbook with this code to work on it, you have to stop the code executing the Application.Quit line. One way to do this to rename or move TheBookToPrint.xls. When the code tries to open TheBookToPrint.xls, it won't find it and will pop up an error. At that point you could stop the code and work on the file.

Hope that helps some...

Report •

Ask Question