Save As

[cvach]

I would like to know if there is a way to save a file automatically
based on a name or value in a specifc cell on the worksheet. For
example, cell B5 contains the value 22. When I go to save can I have
it automatically save the file as 22.xls

 

[Dave Peterson]

You could use a macro:

option explicit
sub testme()
with activeworkbook
.saveas filename:=.path & "\" & .worksheets("sheet99").range("b5").value, _
fileformat:=xlworkbooknormal
end with
end sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[cvach]

I sorry, I was mistaken when I said that I wanted to save it. What I
want to do is print a portion of the sheet as a .pdf and send it to a
folder named Invoices. I would like the .pdf to have the name of the
value in b5. Any other ideas?

 

[Dave Peterson]

I think it depends on what you use to create pdf files.

Sorry, I don't know enough about them to help.

 

[Pete_UK]

If you have a PDF writer, like Adobe Acrobat, you select the PDF
Printer and then print your sheet using that driver - it results in a
PDF file and you can specify (manually) which folder you want the file
saved in.

Hope this helps.

Pete

 

[Dave Peterson]

Can you specify the filename in a macro with Adobe Acrobat or is that portion a
manual effort?

 

[Pete_UK]

Dave,

there are various options that can be set up in the Adobe PDF driver -
one relates to Adobe Output Folder and there are 2 choices: "My
Docs\*.pdf" and "Prompt for filename".

In the first case it will take the .xls filename and save
automatically, but saves to My Documents which I didn't want - there
were several hundred of these to do each month and I had set up a
folder structure to handle this. In the second case it assumes the .xls
filename but you can change this and the folder - once the folder is
set for the first one, subsequent PDFs remember this, but you still
have to click "Save" for each one. This is what I ended up doing (well,
the client does it!), but it's a bit tedious clicking "Save" a few
hundred times, so I always felt I would come back to this if I
discovered another way of doing it - thanks for your other post btw.

Pete

 

[Dave Peterson]

If you test that other stuff, post back with your results.

(I don't own Acrobat, but it'll be good to let google have your results.)

 

[Pete_UK]

Yeah, will do - I've become a "regular" now!

Pete