Consulting

Results 1 to 8 of 8

Thread: Solved: RmDir generates File/Path Access Error

  1. #1
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,249
    Location

    Solved: RmDir generates File/Path Access Error

    I'm getting a runtime error 75 - File/Path access error on the RmDir

    This is just the skelton of some code going into a larger project.

    We get ZIP files with a single CSV inside. I wanted to avoid having to un-ZIP the file to read the CSV by transparently allowing the user to just 'read' the zip file. The final (if I ever get there) macro will select either the zip or a csv file. If a zip file, then extract the csv, process the csv, and then clean up and leave just the original zip.

    Every thing seems to be fine, except the RmDir generates a RT error 75, and if I ignore it, it leaves an empty folder.

    I keep thinking that I'm doing something that is locking the folder. It is empty (the 'Kill' works) and the temp folder is gone, so I can't see where I've gone astray.



    [VBA]
    Option Explicit
    'http://www.rondebruin.nl/win/s7/win002.htm
    Sub Unzip2()
    Dim oFileSystemObject As Object
    Dim oShellApplication As Object
    Dim vFileName As Variant
    Dim FileNameFolder As Variant
    Dim i As Long


    vFileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip,Extract (*.csv), *.csv", MultiSelect:=False)
    If vFileName = False Then Exit Sub

    'selected a ZIP
    If LCase(Right(vFileName, 3)) = "zip" Then

    i = InStrRev(vFileName, Application.PathSeparator)
    FileNameFolder = Left(vFileName, i) & Format(Now, "yymmdd-hhmmss") & Application.PathSeparator


    'Make the normal folder in DefPath
    MkDir FileNameFolder


    'Extract the files into the newly created folder
    Set oShellApplication = CreateObject("Shell.Application")
    oShellApplication.Namespace(FileNameFolder).CopyHere oShellApplication.Namespace(vFileName).items

    'if ZIP, must contain a single file
    If oShellApplication.Namespace(vFileName).items.Count > 1 Then
    MsgBox "Must be one file in Zip"
    Set oShellApplication = Nothing

    Else
    Set oShellApplication = Nothing

    'get the name of the one file
    vFileName = Dir(FileNameFolder)

    'must be a CSV file
    If LCase(Right(vFileName, 3)) <> "csv" Then
    MsgBox "file in CSV is not csv"

    Else
    MsgBox "Real processing will go here" & vbCrLf & vbCrLf & FileNameFolder & vFileName

    ' On Error Resume Next
    Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")
    oFileSystemObject.DeleteFolder Environ("Temp") & "\Temporary Directory*", True
    Set oFileSystemObject = Nothing
    On Error GoTo 0

    'delete the only file in the folder
    Kill FileNameFolder & vFileName

    'remove the folder ------------------- ^%&*&%$#@$#%$&^(*%$#@
    'runtime error 75 - File/Path access error
    RmDir FileNameFolder

    End If

    End If

    'selected a CSV
    ElseIf LCase(Right(vFileName, 3)) = "csv" Then
    MsgBox "Real processing will go here" & vbCrLf & vbCrLf & FileNameFolder & vFileName

    'selected something else
    Else
    MsgBox "some other kind of file was read"
    End If

    End Sub
    [/VBA]

    Paul

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,422
    A hidden folder has been left in the directory.

    This answer isn't correct at second sight.
    Last edited by snb; 05-14-2013 at 12:19 PM.

  3. #3
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,693
    Location
    Did you leave out a backslash?
    [VBA]FileNameFolder & "\" & vFileName [/VBA]

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,840
    Location
    [VBA]FileNameFolder = Left(vFileName, i) & Format(Now, "yymmdd-hhmmss") '& Application.PathSeparator [/VBA]
    Please take the time to read the Forum FAQ

  5. #5
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,249
    Location
    Sorry - been fighting "The server is too busy at the moment. Please try again later." all day

    Thanks to all for looking at it

    snb -- nope, no hidden files or folders. I checked

    Ken / SamT-- the comes "\' comes from

    i = InStrRev(vFileName, Application.PathSeparator)

    and seems not to be the problem

    But .. (ta da) ... I think I figured out why something was holding the folder open: It was the

    [vba]
    vFileName = Dir(FileNameFolder)
    [/vba]

    line.

    When I added a

    [vba]
    x = Dir() ' might have been Dir(""), PC's at work. fuzzy memory
    [/vba]

    after storing the name of the actual file, that seemed to unlock the folder and the RmDir deleted the folder. I didn't see anything about that in the on line help, but I think that was the issue.

    Planning some more and more rigourous testing tomorrow

    Paul

  6. #6
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,693
    Location
    IF the folder just has the one file, a path and *.* will find it. e.g.
    [VBA]Sub DirFiles()
    Dim FileName As String, FileSpec As String, FileFolder As String
    Dim wb As Workbook

    FileFolder = ThisWorkbook.Path & "\"
    FileSpec = FileFolder & "*.xl*"

    FileName = Dir(FileSpec)
    If FileName = "" Then Exit Sub

    ' Loop until no more matching files are found
    Do While FileName <> ""
    If IsWorkbookOpen(FileName) = False Then
    Set wb = Workbooks.Add(FileFolder & FileName)
    DoEvents
    wb.Close True
    End If
    FileName = Dir()
    Loop

    End Sub[/VBA]

  7. #7
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,422
    I experienced the same problem you (Paul) did.
    The folder seems to be 'occopied'

    By the way the code is a rather stragne mix of all kinds of libraries. Why not using

    [VBA]
    with createobject("scripting.filesytemobject")
    if .getextensionname(vfilename) ="zip" then
    filenamefolder=.getparentfoldername(vfilename) & Application.PathSeparator & Format(Now, "yymmdd-hhmmss") & Application.PathSeparator

    ----
    .deletefolder filenamefolder

    end with
    [/VBA]

  8. #8
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    6,249
    Location
    By the way the code is a rather strange mix of all kinds of libraries.
    That's because I 'cooked' it up using 2 different 'recipes' I found on line

    You're right, and I'll clean it up before actually trying to use it

    Paul
    Last edited by Paul_Hossler; 05-15-2013 at 06:46 AM.

Posting Permissions

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