Save to directory and create if not exist

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

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
 
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
 
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
 
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
 
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)
 
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
 
Thanks Ron and Bob, both have given options that work. It'll be awhile
before I understand which is the preferred option!

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)
 
Back
Top