Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Your code works perfectly for me.
Try..
If UCase(Cells(i, 1).Value) = "X" Then

so that if you use a capital or small 'x', it'll still work.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Gary,

Thanks for your help, it works on mine too. It's just I did not know it was
my External Drive. I however had another issue with RmDir Cells(i, 1).Value.

The issue is that this commadn is supposed to delete the empty folder (if it
is empty only), so the issue is to retrieve folder path which I cant, I am
only showing the whole path instead of just the folder path. Can can I do
this?


"Gary Brown" wrote:

Your code works perfectly for me.
Try..
If UCase(Cells(i, 1).Value) = "X" Then

so that if you use a capital or small 'x', it'll still work.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Create a UDF called FolderName (See below) then change the RmDir statement to
read...

'delete the folder also if it's empty
RmDir FolderName(Cells(i, 1).Value)

'/=========================================/
' Function Purpose:
' get folder from full path and file name
'
'/=========================================/
'
Public Function FolderName(strFolder As String) As String
Dim i As Integer
Dim sResult As String

On Error GoTo err_Function

sResult = ""

If Len(strFolder) < 0 Then
For i = Len(strFolder) To 1 Step -1
If Mid(strFolder, i, 1) = Application.PathSeparator Then
sResult = Left(strFolder, i - 1)
Exit For
End If
Next i
End If

FolderName = sResult

exit_Function:
On Error Resume Next
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: FolderName - " & Now()
GoTo exit_Function

End Function
'/=========================================/



--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Gary,

Thanks for your help, it works on mine too. It's just I did not know it was
my External Drive. I however had another issue with RmDir Cells(i, 1).Value.

The issue is that this commadn is supposed to delete the empty folder (if it
is empty only), so the issue is to retrieve folder path which I cant, I am
only showing the whole path instead of just the folder path. Can can I do
this?


"Gary Brown" wrote:

Your code works perfectly for me.
Try..
If UCase(Cells(i, 1).Value) = "X" Then

so that if you use a capital or small 'x', it'll still work.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Gary,

Could not implement it. What is UDF? and here's how we look so far:



'-----------------Code Starts-----------------

Sub LargoFiles()
'Use this to delete files and the whole folder
'Note: RmDir delete only a empty folder
Dim lRow As Long
Dim i As Long
Dim Fshij As String
Dim Folderin As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 4 To lRow
If LCase(Cells(i, 1).Value) = "x" Then
Fshij = Cells(i, 2) '.Value
Kill Fshij 'delete all files in the folder
RmDir FolderName(Cells(i, 1).Value) 'delete folder if empty
Cells(i, 1).Value = "Deleted"
End If
Next i

On Error GoTo 0

End Sub


'/=========================================/
' Function Purpose:
' get folder from full path and file name
' Gary Brown's help
'/=========================================/
'
Public Function FolderName(strFolder As String) As String
Dim i As Integer
Dim sResult As String

On Error GoTo err_Function

sResult = ""

If Len(strFolder) < 0 Then
For i = Len(strFolder) To 1 Step -1
If Mid(strFolder, i, 1) = Application.PathSeparator Then
sResult = Left(strFolder, i - 1)
Exit For
End If
Next i
End If

FolderName = sResult

exit_Function:
On Error Resume Next
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: FolderName - " & Now()
GoTo exit_Function

End Function
'/=========================================/
'-----------------Code Ends-----------------




Thanks for all the help provided,
Adnan





"Gary Brown" wrote:

Create a UDF called FolderName (See below) then change the RmDir statement to
read...

'delete the folder also if it's empty
RmDir FolderName(Cells(i, 1).Value)

'/=========================================/
' Function Purpose:
' get folder from full path and file name
'
'/=========================================/
'
Public Function FolderName(strFolder As String) As String
Dim i As Integer
Dim sResult As String

On Error GoTo err_Function

sResult = ""

If Len(strFolder) < 0 Then
For i = Len(strFolder) To 1 Step -1
If Mid(strFolder, i, 1) = Application.PathSeparator Then
sResult = Left(strFolder, i - 1)
Exit For
End If
Next i
End If

FolderName = sResult

exit_Function:
On Error Resume Next
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: FolderName - " & Now()
GoTo exit_Function

End Function
'/=========================================/



--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Gary,

Thanks for your help, it works on mine too. It's just I did not know it was
my External Drive. I however had another issue with RmDir Cells(i, 1).Value.

The issue is that this commadn is supposed to delete the empty folder (if it
is empty only), so the issue is to retrieve folder path which I cant, I am
only showing the whole path instead of just the folder path. Can can I do
this?


"Gary Brown" wrote:

Your code works perfectly for me.
Try..
If UCase(Cells(i, 1).Value) = "X" Then

so that if you use a capital or small 'x', it'll still work.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan

Gary,

It worked. Last night I must have been asleep and not noticed that
column index should have been 2 instead of 1 in code line "RmDir
FolderName(Cells(i, 1).Value)". Just corrected it and it works just fine. I
do appreciate all your time in helping me work this thru.

Once again, thank you, Gary!
Adnan


"Gary Brown" wrote:

Create a UDF called FolderName (See below) then change the RmDir statement to
read...

'delete the folder also if it's empty
RmDir FolderName(Cells(i, 1).Value)

'/=========================================/
' Function Purpose:
' get folder from full path and file name
'
'/=========================================/
'
Public Function FolderName(strFolder As String) As String
Dim i As Integer
Dim sResult As String

On Error GoTo err_Function

sResult = ""

If Len(strFolder) < 0 Then
For i = Len(strFolder) To 1 Step -1
If Mid(strFolder, i, 1) = Application.PathSeparator Then
sResult = Left(strFolder, i - 1)
Exit For
End If
Next i
End If

FolderName = sResult

exit_Function:
On Error Resume Next
Exit Function

err_Function:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Function: FolderName - " & Now()
GoTo exit_Function

End Function
'/=========================================/



--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Gary,

Thanks for your help, it works on mine too. It's just I did not know it was
my External Drive. I however had another issue with RmDir Cells(i, 1).Value.

The issue is that this commadn is supposed to delete the empty folder (if it
is empty only), so the issue is to retrieve folder path which I cant, I am
only showing the whole path instead of just the folder path. Can can I do
this?


"Gary Brown" wrote:

Your code works perfectly for me.
Try..
If UCase(Cells(i, 1).Value) = "X" Then

so that if you use a capital or small 'x', it'll still work.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Correct, the myFName includes the whole path of the file (i.e.:
C:\Temp\MyFile.xls). What it is, is a list of file paths that I extracted
from a folder with subfolders, these paths are from B3 and downwards, I had
to have the loop going thru them and deleting the once marked with an X in
column A.

Thanks for your quick response Gary,
Adnan



"Gary Brown" wrote:

I'm guessing that your mFName does not include the folder.
To work, the kill method should include the entire path and name to
guarantee that the KILL statement can find the correct file.

ie: Kill C:\Temp\MyFile.xls
instead of
Kill MyFile.xls
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Adnan" wrote:

Hi to all,

Could anyone please tell me as to why the following code won't work?


Sub LargoFiles()

Dim lRow As Long
Dim i As Long
Dim myFName As String

With ActiveSheet
lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

On Error Resume Next

For i = 3 To lRow
If Cells(i, 1).Value = "x" Then
myFName = Cells(i, 2).Value
Kill myFName ' delete files specified with an x
RmDir Cells(i, 1).Value 'delete the folder also
if it's empty
Cells(i, 1).Value = "Deleted" 'mark row as deleted
End If
Next i

On Error GoTo 0

End Sub



Would appreciate any help provided,
Adnan