auto printing

[barachiel]

Everyday we're printing dtr forms and preventive
maintenance sheets. All in all about 35 files we have to
open and just print.

Is it possible to just list all these filenames in one
sheet and click on the filename and it will automatically
open and print? They said its possible thru macro or
visual basic but i don't have the slightest idea to use it.

Any help will be much appreciated.

Thank you in advance

barachiel

 

[Andy Brown]

barachiel,

You didn't get a reply. I expect this *can* be done but don't know how.
However, I do know you don't have to open files to print them.

In the File -- Open dialog, you can right-click a file & Print. Also, you
can select several files first & do the same. To select files "in a row",
click the first then hold down Shift & click the last. To select individual
files, use CTRL+Click.

HTH,
Andy

 

[Ron de Bruin]

Try this with the filenames in Sheets("Sheet1").Range("a1:a35")
<yourfile.xls>

The path in this example is C:\

Sub test()
Dim wb As Workbook
Dim cell As Range
For Each cell In Sheets("Sheet1").Range("a1:a35") _
.SpecialCells(xlCellTypeConstants)
If Dir("c:\" & cell.Value) <> "" Then
Application.ScreenUpdating = False
Set wb = Workbooks.Open("c:\" & cell.Value)
wb.PrintOut
wb.Close False
Application.ScreenUpdating = True
End If
Next
End Sub

 

[barachiel]

Ron,

Im not familiar in using macro, can you show me the way to
apply this code, thru a step by step means.

barachiel

-----Original Message-----
Try this with the filenames in Sheets("Sheet1").Range ("a1:a35")
<yourfile.xls>

The path in this example is C:\

Sub test()
Dim wb As Workbook
Dim cell As Range
For Each cell In Sheets("Sheet1").Range("a1:a35") _
.SpecialCells(xlCellTypeConstants)
If Dir("c:\" & cell.Value) <> "" Then
Application.ScreenUpdating = False
Set wb = Workbooks.Open("c:\" & cell.Value)
wb.PrintOut
wb.Close False
Application.ScreenUpdating = True
End If
Next
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"barachiel" <[email protected]> wrote in

message news:[email protected]...

 

[barachiel]

Andy,

Thank you for the additional information.

barachiel

 

[Ron de Bruin]

Hi

1) The workbook names are in Sheets("Sheet1").Range("a1:a35")
Like this yourfile.xls

2) The path to the filse is in the example C:\ (change to yours)


3) Where do you place this Macro???

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 in Excel you get a list of your macro's
Select "test" and press Run

Post back if you need more help

 

[barachiel]

Ron,

Everything works perfectly!

Selecting "test" after F8 does not do any printing.

Again following your instruction, pressing Alt-F11 then
Alt-F8 another filename has come out "sheet1.test",
choosing that name and pressing run, all files written on
that range started printing. Great!!!

If it is not much to ask, I have a question.
Is it possible to have a choice of just choosing
(clicking )a specific filename from the list and not all
printing at the same time.

Again, thank you so much for help.

barachiel

-----Original Message-----
Hi

1) The workbook names are in Sheets("Sheet1").Range ("a1:a35")
Like this yourfile.xls

2) The path to the filse is in the example C:\ (change to yours)


3) Where do you place this Macro???

Alt-F11
Insert>Module from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you do Alt-F8 in Excel you get a list of your macro's
Select "test" and press Run

Post back if you need more help

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




"barachiel" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi barachiel

"sheet1.test"

The macro is now in a sheet module and not in a normal module

Follow the steps again in a new workbook to get it right.

If it is not much to ask, I have a question.
Is it possible to have a choice of just choosing
(clicking )a specific filename from the list and not all
printing at the same time.

If you run this macro it will print the workbook that is in the activecell

Sub Test2()
Dim wb As Workbook
If Dir("c:\" & ActiveCell.Value) <> "" Then
Application.ScreenUpdating = False
Set wb = Workbooks.Open("c:\" & cell.Value)
wb.PrintOut
wb.Close False
Application.ScreenUpdating = True
End If
End Sub


If you want it to work when you select a cell then you can use the SelectionChange event
in the Sheet module

Check out this site about events
http://www.cpearson.com/excel/events.htm


Right click on the sheet tab and choose view code
Paste the code there
Alt-Q to go back to Excel

If you select a cell in A1:A35 the macro "Test2" will run

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A35"), Target) Is Nothing Then
Test2
End If
End Sub

 

[Ron de Bruin]

Typo in the macro

Set wb = Workbooks.Open("c:\" & cell.Value)

must be

Set wb = Workbooks.Open("c:\" & ActiveCell.Value)

 

[barachiel]

Thank you Ron,

I'll check Mr.Chip Pearson website later on.


barachiel