help with making a directory and saving the spreadsheet

[[email protected]]

assume the folowing spreadsheet

1
A Bristol
B r-11111
C 1st april 2006

I want to be able to click on a button and it to do the following:

1. check to see if a path exists called c:\ and the contents of cell
A1 ie c:\bristol, if not then create one.

2. save the spreadsheet with the name cell a1 + cell a2 + the month and
year of cell a3.xls ie Bristol r-11111 april 2006.xls and put it into
the above directory.

how can i do this?

[Tom Ogilvy]

Probably something like this:

Dim s as String, s1 as String
On error Resume next
s = "C:\" & ActiveSheet.Range("A1").Text
mkdir s
s = s & "\"
s1 = Range("A1").Text _
& " " & Range("A2").Text _
& " " & format(Range("A3"),"mmm yyyy") & ".xls"
activeworkbook.SaveAs s & s1
On Error goto 0
if dir(s & s1) <> "" then
msgbox s1 & " successfully saved"
else
msgbox "Problems "
End if


--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> assume the folowing spreadsheet
>
> 1
> A Bristol
> B r-11111
> C 1st april 2006
>
> I want to be able to click on a button and it to do the following:
>
> 1. check to see if a path exists called c:\ and the contents of cell
> A1 ie c:\bristol, if not then create one.
>
> 2. save the spreadsheet with the name cell a1 + cell a2 + the month and
> year of cell a3.xls ie Bristol r-11111 april 2006.xls and put it into
> the above directory.
>
> how can i do this?
>
>

[[email protected]]

I'll give that a go. thx

[[email protected]]

worked great. Inow realise why I couldn't get my code to work -
forgot the backslash

thx once again