Adding files to a print queue

Hello, I have end users who have to print a lot of contracts through excel.
What they are wanting to do is add the contracts to a sort of print queue.
(that way they can print all files at once) My thought is to have a seperate
file (based on the users name) updated anytime the user wants to add the file
to the queue. In the file would just be a list of file names underneath the
"A" Column. Then, when the user wants to print from the queue they would be
able to hit one button and all the files in the queue would be sent to the
printer. I have a macro that I would want it to run since it prints out the
necessary pages from each file. So my questions are: Can VBA select and print
files from a list of names from one spreadsheet and then loop through each
name/file and print in order with running my "finalize" macro?

Thanks for any given help!

Dim sh as Worksheet
Dim rng as Range
Dim bk as Workbook
Dim cell as Range
set sh = Workbooks("List.xls").Worksheets("Sheet1")

set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,1).End(xldown))

for each cell in rng
set bk = Workbooks.Open(cell.name)
' bk is now the activeworkbook, so run you macro
' macro that prints
bk.close Savechanges:=False
Next

--
Regards,
Tom Ogilvy


"Brad" <[email protected]> wrote in message
news:[email protected]...
> Hello, I have end users who have to print a lot of contracts through
excel.
> What they are wanting to do is add the contracts to a sort of print queue.
> (that way they can print all files at once) My thought is to have a
seperate
> file (based on the users name) updated anytime the user wants to add the
file
> to the queue. In the file would just be a list of file names underneath
the
> "A" Column. Then, when the user wants to print from the queue they would
be
> able to hit one button and all the files in the queue would be sent to the
> printer. I have a macro that I would want it to run since it prints out
the
> necessary pages from each file. So my questions are: Can VBA select and
print
> files from a list of names from one spreadsheet and then loop through each
> name/file and print in order with running my "finalize" macro?
>
> Thanks for any given help!

Thanks Tom,

Another question. These files will be in different folders. Would it be
better to have the "List.xls" file reference the full path and name of the
file into the cell? If so, how would I do this? Thanks.

"Tom Ogilvy" wrote:

> Dim sh as Worksheet
> Dim rng as Range
> Dim bk as Workbook
> Dim cell as Range
> set sh = Workbooks("List.xls").Worksheets("Sheet1")
>
> set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,1).End(xldown))
>
> for each cell in rng
> set bk = Workbooks.Open(cell.name)
> ' bk is now the activeworkbook, so run you macro
> ' macro that prints
> bk.close Savechanges:=False
> Next
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brad" <[email protected]> wrote in message
> news:[email protected]...
> > Hello, I have end users who have to print a lot of contracts through
> excel.
> > What they are wanting to do is add the contracts to a sort of print queue.
> > (that way they can print all files at once) My thought is to have a
> seperate
> > file (based on the users name) updated anytime the user wants to add the
> file
> > to the queue. In the file would just be a list of file names underneath
> the
> > "A" Column. Then, when the user wants to print from the queue they would
> be
> > able to hit one button and all the files in the queue would be sent to the
> > printer. I have a macro that I would want it to run since it prints out
> the
> > necessary pages from each file. So my questions are: Can VBA select and
> print
> > files from a list of names from one spreadsheet and then loop through each
> > name/file and print in order with running my "finalize" macro?
> >
> > Thanks for any given help!
>
>
>

Of course you would need to provide all the information to access the file
in each cell.

For example in A1 I would type

"C:\MyFolder\MySubFolder\Myfile.xls"

--
Regards,
Tom Ogilvy

"Brad" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom,
>
> Another question. These files will be in different folders. Would it be
> better to have the "List.xls" file reference the full path and name of the
> file into the cell? If so, how would I do this? Thanks.
>
> "Tom Ogilvy" wrote:
>
> > Dim sh as Worksheet
> > Dim rng as Range
> > Dim bk as Workbook
> > Dim cell as Range
> > set sh = Workbooks("List.xls").Worksheets("Sheet1")
> >
> > set rng = sh.Range(sh.Cells(1,1),sh.Cells(1,1).End(xldown))
> >
> > for each cell in rng
> > set bk = Workbooks.Open(cell.name)
> > ' bk is now the activeworkbook, so run you macro
> > ' macro that prints
> > bk.close Savechanges:=False
> > Next
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Brad" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hello, I have end users who have to print a lot of contracts through
> > excel.
> > > What they are wanting to do is add the contracts to a sort of print
queue.
> > > (that way they can print all files at once) My thought is to have a
> > seperate
> > > file (based on the users name) updated anytime the user wants to add
the
> > file
> > > to the queue. In the file would just be a list of file names
underneath
> > the
> > > "A" Column. Then, when the user wants to print from the queue they
would
> > be
> > > able to hit one button and all the files in the queue would be sent to
the
> > > printer. I have a macro that I would want it to run since it prints
out
> > the
> > > necessary pages from each file. So my questions are: Can VBA select
and
> > print
> > > files from a list of names from one spreadsheet and then loop through
each
> > > name/file and print in order with running my "finalize" macro?
> > >
> > > Thanks for any given help!
> >
> >
> >