+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    07-18-2006
    Posts
    73

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

    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?

    In the end I want to check the folder to see if it exists and save there but ask if I want to overwrite the file if a file by the same name already exists. The overwrite the file part I was helped with on this forum already just trying to put it into action to go with everything.

    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
    Last edited by nbaj2k; 08-11-2006 at 11:31 AM.

  2. #2
    Ron de Bruin
    Guest

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

    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


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



    "nbaj2k" <[email protected]> wrote in message
    news:[email protected]...
    >
    > 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
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=570801
    >




  3. #3
    ChasAA
    Guest

    RE: Save file in a new folder, but create folder only if folder doesn'

    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" wrote:

    >
    > 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
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=570801
    >
    >


  4. #4
    Registered User
    Join Date
    07-18-2006
    Posts
    73

    but how would I put them together? :(

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

    The code below is my attempt at bringing them together, I believe I'm far off though, could someone take a look?

    Thanks,

    ~J


    Dim dirstr As String
    Dim wb As Workbook

    Set wb = ActiveWorkbook

    dirstr = "G:\Reports\Friday Reports\" & MyDate & "\"
    If Not DirectoryExist(dirstr) Then
    MkDir dirstr
    'wb.SaveAs dirstr & "\" & filename
    'Test
    Dim res As VbMsgBoxResult



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

    If res = vbYes Then
    ChDir dirstr
    wb.SaveAs dirstr & "\" & filename
    Else
    End If







    'End Test
    Else
    Dim FName As String

    Dim res2 As VbMsgBoxResult



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

    If res2 = vbYes Then
    ChDir dirstr
    wb.SaveAs dirstr & "\" & filename
    Else
    'do nothing?
    End If

    wb.SaveAs dirstr & "\" & filename
    End If
    Last edited by nbaj2k; 08-11-2006 at 01:43 PM.

  5. #5
    ChasAA
    Guest

    Re: Save file in a new folder, but create folder only if folder do

    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" wrote:

    >
    > I got this code, my now I'm confused how I would also incorporate into
    > this how to have it check to see if the file already exists. If it
    > 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 confused
    > how I would put them both together. Is there an Else If statement I
    > 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 Sub
    >
    >
    > --
    > nbaj2k
    > ------------------------------------------------------------------------
    > nbaj2k's Profile: http://www.excelforum.com/member.php...o&userid=36480
    > View this thread: http://www.excelforum.com/showthread...hreadid=570801
    >
    >


  6. #6
    Registered User
    Join Date
    07-18-2006
    Posts
    73

    my previous post

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

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

    Thanks,

    ~J

  7. #7
    Registered User
    Join Date
    07-18-2006
    Posts
    73

    I got it!

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

    ~J

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1