Hi Rob

I test it now and it is working for me
But you can't use

dirstr = "C:\ron\excel"

If ron not exist


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rob" wrote in message ...
Ron,

Thanks for this I was wondering why I couldn't find DirectoryExist in the help!
Also, the following isn't working, MkDir dirstr Any ideas?

Thanks, Rob

"Ron de Bruin" wrote in message ...
Wake up ron

Here is the function that you need also

Function DirectoryExist(sstr As String)
'Tom Oglivy
Dim lngAttr As Long
DirectoryExist = False
If Dir(sstr, vbDirectory) < "" Then
lngAttr = GetAttr(sstr)
If lngAttr And vbDirectory Then _
DirectoryExist = True
End If
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ron de Bruin" wrote in message ...
Hi Rob

Untested

Sub Test()
Dim dirstr As String
Dim wb As Workbook

ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb.Sheets(1).UsedRange
.Value = .Value
End With

dirstr = "C:\my documents"
If Not DirectoryExist(dirstr) Then
MkDir dirstr
wb.SaveAs dirstr & "\ron.xls"
Else
wb.SaveAs dirstr & "\ron.xls"
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Rob" wrote in message ...
Hi,

Does anyone have some sample code to help me achieve the following: copy a sheet to a new workbook with contents as values as
opposed to formula, then save the new workbook in a directory (e.g. C:\my documents\) but if the directory doesn't exist,
create the directory and then save the file. I would like to do this in one routine if possible.

Thanks, Rob