Ladies & Gents,

I'm trying to make a macro to do a few tasks, and have come to a halt as I
can't seem to find an answer to my questions. I hope someone can help me
out.

I want this macro to do the following;
1) - Create a work list
2) - Use that list to open datasets with similar names, and save them as
excel reports (2 off for each name in the list)
3) - Open those reports & create an invoice for each name in the list
4) - Combine those invoices & the first list into one workbook
5) - Print the invoices, &
6) - Email some of the reports (the remainder go by snail-mail!)

At this point in time, I have code to write most of the macro, but I have
the following questions;
A) - How can I use the list to open other files & spreadsheets? I can add
sheets to a workbook using that list, but I can't seem to find a way to use
the list to open new files.
B) - How do I send a page to print at a network printer, and to a particular
tray? I can find code on printing sheets, but none seem to deal with IP
addresses or printer trays. And,
C) - Can I run the macro with 2 other workbooks open at the same time? It
seems like I can create the invoice workbook if I have the macro going, open
up a report, and copy data to the invoice. But I've always been under the
impression that I can only have the macro & another workbook open at any one
time.

Any help with these questions would be greatly appreciated.


TIA
Andrew.

To open another workbook, you would use a statement like:

Workbooks.Open "full path and file name"

If you leave out the path, Excel looks only in the current directory.

On printing, you can record the command of changing the printer to a network
printer. That then makes it the active printer. I have heard that Excel
2002 and 2003 will allow some additional print commands such as setting
print trays, but I have not tested.

You can have lots of workbooks open when you run a macro. There is no
restriction. However, if you are opening up a workbook that is already
open, you are likely to get an error or lose data loaded into the workbook
if you reopen.

Bob Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Andrew" wrote in message
...
Ladies & Gents,

I'm trying to make a macro to do a few tasks, and have come to a halt as I
can't seem to find an answer to my questions. I hope someone can help me
out.

I want this macro to do the following;
1) - Create a work list
2) - Use that list to open datasets with similar names, and save them as
excel reports (2 off for each name in the list)
3) - Open those reports & create an invoice for each name in the list
4) - Combine those invoices & the first list into one workbook
5) - Print the invoices, &
6) - Email some of the reports (the remainder go by snail-mail!)

At this point in time, I have code to write most of the macro, but I have
the following questions;
A) - How can I use the list to open other files & spreadsheets? I can add
sheets to a workbook using that list, but I can't seem to find a way to
use
the list to open new files.
B) - How do I send a page to print at a network printer, and to a
particular
tray? I can find code on printing sheets, but none seem to deal with IP
addresses or printer trays. And,
C) - Can I run the macro with 2 other workbooks open at the same time? It
seems like I can create the invoice workbook if I have the macro going,
open
up a report, and copy data to the invoice. But I've always been under the
impression that I can only have the macro & another workbook open at any
one
time.

Any help with these questions would be greatly appreciated.


TIA
Andrew.

for printing, use Add a printer and define it to go to have the setting you
need. then print to this printer. (in otherwords, you can have multiple
printers defined for the same physical printer).

--
Regards,
Tom Ogilvy

"Andrew" wrote in message
...
Ladies & Gents,

I'm trying to make a macro to do a few tasks, and have come to a halt as I
can't seem to find an answer to my questions. I hope someone can help me
out.

I want this macro to do the following;
1) - Create a work list
2) - Use that list to open datasets with similar names, and save them as
excel reports (2 off for each name in the list)
3) - Open those reports & create an invoice for each name in the list
4) - Combine those invoices & the first list into one workbook
5) - Print the invoices, &
6) - Email some of the reports (the remainder go by snail-mail!)

At this point in time, I have code to write most of the macro, but I have
the following questions;
A) - How can I use the list to open other files & spreadsheets? I can add
sheets to a workbook using that list, but I can't seem to find a way to
use
the list to open new files.
B) - How do I send a page to print at a network printer, and to a
particular
tray? I can find code on printing sheets, but none seem to deal with IP
addresses or printer trays. And,
C) - Can I run the macro with 2 other workbooks open at the same time? It
seems like I can create the invoice workbook if I have the macro going,
open
up a report, and copy data to the invoice. But I've always been under the
impression that I can only have the macro & another workbook open at any
one
time.

Any help with these questions would be greatly appreciated.


TIA
Andrew.

"Bob Flanagan" wrote in message
...
You can have lots of workbooks open when you run a macro. There is no
restriction. However, if you are opening up a workbook that is already
open, you are likely to get an error or lose data loaded into the workbook
if you reopen.

In my inexperience, I got myself into trouble with multiple workbooks
opening and closing. As far as I could track it, I was using ActiveWorkbook
and ActiveWorksheet, but the way the code was written the focus kept
changing. I set objects to the Active item as soon as it was activated,
then used the object reference. Also, forgetting to release that reference
gave me some fits, too. Hope you have better luck than I did!

Ed

Bob/Ed/Tom,

Thanks for your answers, but I would just like to clarify one of Bob's.

If I have a value in an excel sheet cell, which is part of a list, and I
give it a name, (eg, Dim MyCellValueName as String), I can use the
statement;
Workbooks.Open "full path and MyCellValueName.xls"
to open a spreadsheet. I can then go to the next cell in that list, give it
the same name, and open up another spreadsheet. If I am mistaken here,
please let me know.

And I understand how to record the printing. One of my problems is that I'm
writing the macros at home, to be used at work. So I'll reset my default
printer, and record a printout being sent to the correct printer & tray. I
am also using Office 97 (my extremely large employer's choise, so please be
gentle with me), so I'll see how I go.

As for the multiple workbooks, they'll all have names, so it shan't pose a
problem if I save the workbooks name when I open & close it.



Thanks again
Andrew


Ed wrote in message ...
"Bob Flanagan" wrote in message
...
You can have lots of workbooks open when you run a macro. There is no
restriction. However, if you are opening up a workbook that is already
open, you are likely to get an error or lose data loaded into the workbook
if you reopen.

In my inexperience, I got myself into trouble with multiple workbooks
opening and closing. As far as I could track it, I was using ActiveWorkbook
and ActiveWorksheet, but the way the code was written the focus kept
changing. I set objects to the Active item as soon as it was activated,
then used the object reference. Also, forgetting to release that reference
gave me some fits, too. Hope you have better luck than I did!

Ed

"Andrew" wrote in message
...
If I have a value in an excel sheet cell, which is part of a list, and I
give it a name, (eg, Dim MyCellValueName as String), I can use the
statement;
Workbooks.Open "full path and MyCellValueName.xls"
to open a spreadsheet. I can then go to the next cell in that list, give
it
the same name, and open up another spreadsheet. If I am mistaken here,
please let me know.

I have an object wb2 Dim'd at the start, and then use
Set wb2 = Workbooks.Open(strFPath & strFName)

Ed