Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object. This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not be
deleted for any reason (such as if file is read only or already opened in an
application) then I get 'permission denied' error. At this point I want to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically in
Excel macro.

Thanks in advance

Nayan

The best way would be to not delete anything, but to build an array of all
deletable files. When you get the error, just quit, if you don't, iterate
the array and delete them all.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nayan" wrote in message
...
Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object. This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not be
deleted for any reason (such as if file is read only or already opened in
an
application) then I get 'permission denied' error. At this point I want to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically in
Excel macro.

Thanks in advance

Nayan

Bob,

Thanks for the idea.

Can you please let me know how to check if a file is deletable or not before
deleting it.

Thanks again

Nayan

"Bob Phillips" wrote:

The best way would be to not delete anything, but to build an array of all
deletable files. When you get the error, just quit, if you don't, iterate
the array and delete them all.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nayan" wrote in message
...
Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object. This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not be
deleted for any reason (such as if file is read only or already opened in
an
application) then I get 'permission denied' error. At this point I want to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically in
Excel macro.

Thanks in advance

Nayan

In the code that you have, I presume that there is a test that traps the
error. AT this point, if and when an error is encountered, then bale out. If
an error is not encountered, add the file path and name to the array, and
continue.

If you get to the end without baling out then the array is full of deletable
items.

Something like this

Option Explicit

Private cnt As Long
Private arfiles
Private FSO As Object

Sub Folders()
Const START_FOLDER = "C:\Test"
Dim i As Long

Set FSO = CreateObject("Scripting.FilesystemObject")
cnt = 0
ReDim arfiles(1 To 1)
If SelectFiles(START_FOLDER) Then
For i = LBound(arfiles) To UBound(arfiles)
Kill arfiles(i)
Next i
End If
End Sub

'-----------------------------------------------------------------------
Public Function SelectFiles(Optional sPath As String) As Boolean
'-----------------------------------------------------------------------
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object

SelectFiles = True
Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
On Error Resume Next
Workbooks.Open Filename:=oFile.Path
On Error GoTo 0
If Err.Number < 0 Then
SelectFiles = False
Exit Function
Else
cnt = cnt + 1
ReDim Preserve arfiles(1 To cnt)
arfiles(cnt) = oFile.Path
ActiveWorkbook.Close savechanges:=False
End If
Next oFile

For Each oSubFolder In oFolder.Subfolders
If Not SelectFiles(oSubFolder.Path) Then
SelectFiles = False
Exit Function
End If
Next

End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nayan" wrote in message
...
Bob,

Thanks for the idea.

Can you please let me know how to check if a file is deletable or not
before
deleting it.

Thanks again

Nayan

"Bob Phillips" wrote:

The best way would be to not delete anything, but to build an array of
all
deletable files. When you get the error, just quit, if you don't, iterate
the array and delete them all.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nayan" wrote in message
...
Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object.
This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not be
deleted for any reason (such as if file is read only or already opened
in
an
application) then I get 'permission denied' error. At this point I want
to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically in
Excel macro.

Thanks in advance

Nayan

Hi Bob,

Thanks for the code you sent.

I have gone through the code and found that if you keep a read only file in
C:\test folder, while deleting that file it will produce "Path/file access"
error.

We need to know ( before kill commnad " Kill arfiles(i)" in the code) if the
file can be deleted or not

The few scenarios in which file can not be deleted are...

1) If its a read only file
2) If its already opened in an application.
3) if its currupted
4) if its a source safe file

Thus just opening a file successfully and adding it to an arrary does not
confirm that we can delete that file.

Any thoughts or suggestions?

Thanks in advance for your help.

Nayan.

"Bob Phillips" wrote:

In the code that you have, I presume that there is a test that traps the
error. AT this point, if and when an error is encountered, then bale out. If
an error is not encountered, add the file path and name to the array, and
continue.

If you get to the end without baling out then the array is full of deletable
items.

Something like this

Option Explicit

Private cnt As Long
Private arfiles
Private FSO As Object

Sub Folders()
Const START_FOLDER = "C:\Test"
Dim i As Long

Set FSO = CreateObject("Scripting.FilesystemObject")
cnt = 0
ReDim arfiles(1 To 1)
If SelectFiles(START_FOLDER) Then
For i = LBound(arfiles) To UBound(arfiles)
Kill arfiles(i)
Next i
End If
End Sub

'-----------------------------------------------------------------------
Public Function SelectFiles(Optional sPath As String) As Boolean
'-----------------------------------------------------------------------
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object

SelectFiles = True
Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
On Error Resume Next
Workbooks.Open Filename:=oFile.Path
On Error GoTo 0
If Err.Number < 0 Then
SelectFiles = False
Exit Function
Else
cnt = cnt + 1
ReDim Preserve arfiles(1 To cnt)
arfiles(cnt) = oFile.Path
ActiveWorkbook.Close savechanges:=False
End If
Next oFile

For Each oSubFolder In oFolder.Subfolders
If Not SelectFiles(oSubFolder.Path) Then
SelectFiles = False
Exit Function
End If
Next

End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nayan" wrote in message
...
Bob,

Thanks for the idea.

Can you please let me know how to check if a file is deletable or not
before
deleting it.

Thanks again

Nayan

"Bob Phillips" wrote:

The best way would be to not delete anything, but to build an array of
all
deletable files. When you get the error, just quit, if you don't, iterate
the array and delete them all.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nayan" wrote in message
...
Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object.
This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not be
deleted for any reason (such as if file is read only or already opened
in
an
application) then I get 'permission denied' error. At this point I want
to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically in
Excel macro.

Thanks in advance

Nayan

I can do you the first two, but I don't know how to do the second two.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Nayan" wrote in message
...
Hi Bob,

Thanks for the code you sent.

I have gone through the code and found that if you keep a read only file
in
C:\test folder, while deleting that file it will produce "Path/file
access"
error.

We need to know ( before kill commnad " Kill arfiles(i)" in the code) if
the
file can be deleted or not

The few scenarios in which file can not be deleted are...

1) If its a read only file
2) If its already opened in an application.
3) if its currupted
4) if its a source safe file

Thus just opening a file successfully and adding it to an arrary does not
confirm that we can delete that file.

Any thoughts or suggestions?

Thanks in advance for your help.

Nayan.

"Bob Phillips" wrote:

In the code that you have, I presume that there is a test that traps the
error. AT this point, if and when an error is encountered, then bale out.
If
an error is not encountered, add the file path and name to the array, and
continue.

If you get to the end without baling out then the array is full of
deletable
items.

Something like this

Option Explicit

Private cnt As Long
Private arfiles
Private FSO As Object

Sub Folders()
Const START_FOLDER = "C:\Test"
Dim i As Long

Set FSO = CreateObject("Scripting.FilesystemObject")
cnt = 0
ReDim arfiles(1 To 1)
If SelectFiles(START_FOLDER) Then
For i = LBound(arfiles) To UBound(arfiles)
Kill arfiles(i)
Next i
End If
End Sub

'-----------------------------------------------------------------------
Public Function SelectFiles(Optional sPath As String) As Boolean
'-----------------------------------------------------------------------
Dim oSubFolder As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object

SelectFiles = True
Set oFolder = FSO.GetFolder(sPath)
Set oFiles = oFolder.Files
For Each oFile In oFiles
On Error Resume Next
Workbooks.Open Filename:=oFile.Path
On Error GoTo 0
If Err.Number < 0 Then
SelectFiles = False
Exit Function
Else
cnt = cnt + 1
ReDim Preserve arfiles(1 To cnt)
arfiles(cnt) = oFile.Path
ActiveWorkbook.Close savechanges:=False
End If
Next oFile

For Each oSubFolder In oFolder.Subfolders
If Not SelectFiles(oSubFolder.Path) Then
SelectFiles = False
Exit Function
End If
Next

End Function


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nayan" wrote in message
...
Bob,

Thanks for the idea.

Can you please let me know how to check if a file is deletable or not
before
deleting it.

Thanks again

Nayan

"Bob Phillips" wrote:

The best way would be to not delete anything, but to build an array of
all
deletable files. When you get the error, just quit, if you don't,
iterate
the array and delete them all.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Nayan" wrote in message
...
Hello,

(Environment Excel 2003) (operating system Windows XP professional)

I am deleting a root folder in a vba macro using filesystem object.
This
root folder has many subfolders in it.

If any of the files (any type of the file) in any subfolder can not
be
deleted for any reason (such as if file is read only or already
opened
in
an
application) then I get 'permission denied' error. At this point I
want
to
rollback the entire deleting process.

Basically on any type of error while deleting the root folder I want
to
rollback the deleting process.

Any ideas or suggestion as how this can be achieved programmetically
in
Excel macro.

Thanks in advance

Nayan