Is it possible to create a macro that saves a file and inserts a file name
which is a combination of the contents of two or more cells on the worksheet.
What I am aiming to do is have a button on a toolbar which will run such a
macro and save a sheet as a pdf document with a name which is a combination
of the contents of two or more cells on the worksheet.
--
paulu

On Thu, 7 Jul 2005 15:14:02 -0700, "paulu"
wrote:

Is it possible to create a macro that saves a file and inserts a file name
which is a combination of the contents of two or more cells on the worksheet.
What I am aiming to do is have a button on a toolbar which will run such a
macro and save a sheet as a pdf document with a name which is a combination
of the contents of two or more cells on the worksheet.

I don't know how you can save a worksheet as a .pdf file since pdf
doesn't appear to be a valid file type in Excel. However you can save
a particular worksheet from a workbook by first copying the contents
of the worksheet to a new workbook, then, assuming A1 contains
"Myfilepart1" and A2 contains "Myfilepart2" - (the two cells
containing your required filename), and with the new workbook as the
active workbook

Use

ActiveWorkbook.SaveAs filename:=Range("a1")&range("a2")

in your code.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard,
Thanks for the reply. After I read it I realised that what I wrote
originally doesn't describe very well what I am doing.

What I am trying to do is automate the following manual steps which occur
after data is entered into the xl worksheet:
1 From the file menu choose Print
2 Select "Adobe PDF" as the printer name (a "Save PDF File as" window opens)
3 Enter a file name that is a combination of two cells from the work sheet
4 Display the pdf file
5 print a hard copy of the file
6 email a copy to a third party
7 return to the worksheet

Is this possible?
Is this the forum to be asking this?

cheers

--
paulu


"Richard Buttrey" wrote:

On Thu, 7 Jul 2005 15:14:02 -0700, "paulu"
wrote:

Is it possible to create a macro that saves a file and inserts a file name
which is a combination of the contents of two or more cells on the worksheet.
What I am aiming to do is have a button on a toolbar which will run such a
macro and save a sheet as a pdf document with a name which is a combination
of the contents of two or more cells on the worksheet.

I don't know how you can save a worksheet as a .pdf file since pdf
doesn't appear to be a valid file type in Excel. However you can save
a particular worksheet from a workbook by first copying the contents
of the worksheet to a new workbook, then, assuming A1 contains
"Myfilepart1" and A2 contains "Myfilepart2" - (the two cells
containing your required filename), and with the new workbook as the
active workbook

Use

ActiveWorkbook.SaveAs filename:=Range("a1")&range("a2")

in your code.

Rgds
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

On Thu, 7 Jul 2005 17:10:02 -0700, "paulu"
wrote:

Richard,
Thanks for the reply. After I read it I realised that what I wrote
originally doesn't describe very well what I am doing.

What I am trying to do is automate the following manual steps which occur
after data is entered into the xl worksheet:
1 From the file menu choose Print
2 Select "Adobe PDF" as the printer name (a "Save PDF File as" window opens)
3 Enter a file name that is a combination of two cells from the work sheet
4 Display the pdf file
5 print a hard copy of the file
6 email a copy to a third party
7 return to the worksheet

Is this possible?
Is this the forum to be asking this?

cheers


I'm a bit out of my depth with this one. However you could no doubt
test whether this is possible by first trying to record a macro of
all the steps down to 5. I can do something similar by printing to a
'Fax' printer.

Presumably to create a .pdf file you need to have an Adobe licence
and the right to do this. I'm not aware that you can create a pdf file
without this, although of course I could be wrong. From the way
you're describing this I guess you have the relevant licence.

If you can capture steps 1-5 in a macro, then adding the code for
steps 6&7 is relatively trivial - see my previous suggestion.

Seems to me this is exactly the right forum. Hopefully someone more
knowledgeable will jump in.

Regards.


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________