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