help with making a directory and saving the spreadsheet

[jeffhughes]

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?

 

[Guest]

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

 

[jeffhughes]

I'll give that a go. thx

 

[jeffhughes]

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

thx once again