Save to directory and create if not exist

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> 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" <[email protected]> wrote in message news:[email protected]...
>>> 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" <[email protected]> wrote in message news:[email protected]...
>>>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> 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" <[email protected]> wrote in message
news:uEcEA%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>> > 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" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> 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" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >>> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:uEcEA%[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> 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" <[email protected]> wrote in message
> >>> news:[email protected]...
> >>> > 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" <[email protected]> wrote in message
> >>> > news:[email protected]...
> >>> >> 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" <[email protected]> wrote in message
> >>> >> news:[email protected]...
> >>> >>> 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
> >>> >>>
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>