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

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

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

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

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

Why not just use

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"
On Error Resume Next
MkDir dirstr
On Error GoTo 0
wb.SaveAs dirstr & "\ron1.xls"
End Sub

Does away with that DirectortyExist function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"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

Hi Bob

Many ways to Rome

I like this one

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\MyDir"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
Else
' do nothing
End If
End Sub


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



"Bob Phillips" wrote in message ...
Why not just use

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"
On Error Resume Next
MkDir dirstr
On Error GoTo 0
wb.SaveAs dirstr & "\ron1.xls"
End Sub

Does away with that DirectortyExist function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"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

Thanks Ron and Bob, both have given options that work. It'll be awhile
before I understand which is the preferred option!

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

Many ways to Rome

I like this one

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\MyDir"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
Else
' do nothing
End If
End Sub


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



"Bob Phillips" wrote in message
...
Why not just use

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"
On Error Resume Next
MkDir dirstr
On Error GoTo 0
wb.SaveAs dirstr & "\ron1.xls"
End Sub

Does away with that DirectortyExist function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"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

Probably not a preferred option, other than personal preference. FWIW, my
choice would not be to introduce FSO unless I had to, or unless the other
code used some Excel specific function (which in this case it doesn't).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" wrote in message
...
Thanks Ron and Bob, both have given options that work. It'll be awhile
before I understand which is the preferred option!

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

Many ways to Rome

I like this one

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\MyDir"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
Else
' do nothing
End If
End Sub


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



"Bob Phillips" wrote in message
...
Why not just use

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"
On Error Resume Next
MkDir dirstr
On Error GoTo 0
wb.SaveAs dirstr & "\ron1.xls"
End Sub

Does away with that DirectortyExist function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"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