Save file in a new folder, but create folder only if folder doesn't already exist?

[nbaj2k]

I am trying to generate reports and save the files in a folder, but I
want to create the folder if it does not already exist. If it does
exist, I want it to just save the file there. Right now I am just
setting it to make a directory, which was fine, but if I have other
reports going to that same folder and the folder already exists, I get
a "Path/File Access error" Is there a little line of code I can put in
that first checks to see if the folder exists?

Thanks,

~J

MyDate = Trim(VBA.Format(Now(), "MM-DD-YY"))
Application.DisplayAlerts = False
MkDir "G:\Reports\Friday Reports\" & MyDate
ActiveWorkbook.SaveAs filename:="G:\Reports\Friday Reports\" & _
MyDate & "\" & filename

 

[Ron de Bruin]

Hi nbaj2k

Use this function
See the test macro how you can save the activeworkbook in the dir C:\MyDir

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

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

Set wb = ActiveWorkbook

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

 

[Guest]

Hello,
Another way is to trap the error in an error routine.
The error number if the directory already exists is 75
Add you sub to include:

Sub makefolder()
On Error GoTo errorTrap:
MyDate = Trim(VBA.Format(Now(), "MM-DD-YY"))
Application.DisplayAlerts = False
MkDir "C:\Reports\Friday Reports\" & MyDate
ActiveWorkbook.SaveAs Filename:="c:\AAAA & " \ " & Filename"
Exit Sub
errorTrap:
If Err.Number = 75 Then
Resume Next
End If

MsgBox Err.Number & " " & Err.Description
End Sub

 

[nbaj2k]

I got this code, my now I'm confused how I would also incorporate int
this how to have it check to see if the file already exists. If i
does I wanted it to ask if it should overwrite or not.

This code was posted in a reply to my other post, but now I'm confuse
how I would put them both together. Is there an Else If statement
would add to it or something?

Thanks,

~J


Public Sub Tester()
Dim FName As String
Const myPath As String = "G:\Reports\First\"
Dim res As VbMsgBoxResult

FName = Range("A1")

If Dir(myPath & FName) <> "" Then
res = MsgBox(Prompt:="The file already exists. " _
& "Do you wish to overwrite it?", _
Buttons:=vbYesNo)
End If

If res = vbYes Then
ChDir myPath
ActiveWorkbook.SaveAs filename:=myPath & FName, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=No, _
CreateBackup:=False
Else
'do nothing?
End If
End Su

 

[Guest]

Hello,
Are you replying to Chas or Ron?.

If it is Chas.
You can call the "makeFolder" subroutine but you will have to pass the
appropriate variables.
Or you can insert the line of code after your line where you assign the
filename.
(Incidentally, but I'm sure you figured it out already, in the snippet I
sent you. I was MkDir with your variable names and Saving as C:\Chasdev etc.
Obviously this is wrong.

What you will be doing now is
Attemting to make the new directory.
If there is an error it will be taken care of by the error trap

Once that is done then you can check to see if the file exisits already

But (I'm not sure about this but why not turn DisplayAlerts on and the
system will ask you if you want to overwrite the file).
If you need alerts off just turn them off straight after you have saved the
file.

Chas

 

[nbaj2k]

I had edited my last post, looks like you responded before I edited i
or something, sorry about that.

I was trying to do it the way that I typed it above. Is it possible t
do that way? Is there an error number for file already existing instea
of directory? I could do an error trap that way.

Thanks,

~

 

[nbaj2k]

Thanks, I just figured it out, greatly appreciate all the help

~J