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

A hidden folder has been left in the directory.

This answer isn't correct at second sight.

Did you leave out a backslash?
[VBA]FileNameFolder & "\" & vFileName [/VBA]

[VBA]FileNameFolder = Left(vFileName, i) & Format(Now, "yymmdd-hhmmss") '& Application.PathSeparator [/VBA]

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

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]

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]

Quote:

---

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