Help with Delete Method

Discussion in 'Scripting' started by Guest, Jul 25, 2003.

  1. Guest

    Guest Guest

    I am working on a script that deletes files that have not
    been accessed for [x] days.

    I have tried the Delete as well as DeleteFile methods
    without success.

    I would appreciate any assistance you may be able to


    'Name: Dave Clark
    'Script_Type: vbscript


    'Takes a drive/folder name from input and returns an Excel
    Spreadsheet listing of folder contents
    'including date last accessed. (Listing will include only
    those files older than value stored in Const intMaxAge)
    'Subracts Date last accesed from the current date.
    'Deletes files older than value stored in variable


    Option Explicit

    ' Create the FileSystem Object
    Dim oFileSys
    Dim Dir, filcnt
    Dim Wedone
    Dim strDirDoneTxt
    Dim strMsgTitle
    Dim strInput
    Dim IExec, strTarget
    Dim objXL, rowCount
    Dim intElapsed, i
    Dim strFilespec, strAFilespec, f

    ' Modify this value To change the maximum allowable number
    of days since file last accessed
    ' Files will be deleted if intElapsed is greater than
    Const intMaxAge = 40

    Set objXL = WScript.CreateObject("Excel.Application")
    For i=1 To 7
    objXL.Columns(i).ColumnWidth = 18

    objXL.Visible = false

    strInput= InputBox("Please Enter a starting folder eg
    c:\temp", "User Input","")

    dir = strInput

    Set oFileSys = CreateObject("Scripting.FileSystemObject")

    objXL.Cells(1,1).Value = "Directory Listing of: " & Dir

    ' Do it!
    GetDir dir,6

    ' Format the top of the spreadsheet

    objXL.Cells(3,1).Value = filcnt-1 & " items in the
    folder " & strInput & " and its sub-folders."

    objXL.Cells(4,3).Formula = "=Sum(" & objXL.Range
    (objXL.Cells(5, 3), objXL.Cells(filcnt+5, 3)).Address
    & ")/1000000"
    objXL.cells(4,5).value="Date Last Accessed"
    objXL.cells(4,6).value="Days Elapsed"
    objXL.cells(4,7).value="Date Deleted"
    'objXL.cells(4,2).HorizontalAlignment = xlRight 'Cannot
    find the hexadecimal version for xlRight


    objXL.Columns("c:c").NumberFormat= "_-* #,##0_-;-* #,##0_-
    ;_-* ""-""??_-;[email protected]_-"

    objXL.Cells(3,5).NumberFormat= "_-* #,##0.000_-;-*
    #,##0.000_-;_-* ""-""??_-;[email protected]_-"
    with objXL.cells(1,1)
    .Font.ColorIndex = 1
    end with


    objXL.Visible = True

    sub GetDir(dir,rowcount)
    dim fh2,fh3,oFolder,oFolders,oFiles,item,Item2
    dim rowDirStart,rowDirEnd,rng1

    set oFolder=oFileSys.GetFolder(dir)
    set oFolders=oFolder.SubFolders
    set oFiles=oFolder.Files

    ' get all sub-folders in this folder
    For each item in oFolders
    ' go to each one
    GetDir item,rowcount

    with objXL.Cells(rowCount,1)
    .Value = ofolder
    .Font.Bold = True
    ' .Interior.Pattern = 1 'xlSolid
    .Font.ColorIndex = 0
    End With

    filcnt = filcnt +1
    rowDirStart = rowCount
    For Each item2 In oFiles
    intElapsed = DiffADate(item2.DateLastAccessed)
    If intElapsed > intMaxAge Then
    objXL.Cells(rowCount,2).Value = item2.Name

    objXL.Cells(rowCount,3).Value = item2.size
    ' objXL.Cells(rowCount,4).Value = item2.type
    objXL.Cells(rowCount,5).Value =
    objXL.Cells(rowCount,6).Value = DiffADate
    objXL.Cells(rowCount,7).Value = Date
    filcnt = filcnt +1
    strAFilespec = oFolder & strFilespec
    ' msgbox (strAFilespec)
    ' strAFileSpec.Delete
    ' oFileSys.DeleteFile(strAFileSpec)
    End If

    End Sub
    ' Release Handle to Excel
    objXL = Nothing

    ' Subtract Date Last Accessed from today's date
    Function DiffADate(theDate)
    DiffADate = DateDiff("d", theDate, now)
    End Function

    ' Delete the file or folder
    Sub DeleteAFile()
    End Sub
    Guest, Jul 25, 2003
    1. Advertisements

  2. Guest

    Yoann Roman Guest

    I am working on a script that deletes files that have not
    You were using an undefined variable. As far as I can tell from quick read,
    strFilespec was never defined to contain the path to the file. So you were
    requesting a delete on an empty variable. See below for the "******" comment
    for the correction. I only posted the GetDir method, which is the one with
    the correction. Also, I assumed everything else worked.
    Yoann Roman, Jul 25, 2003
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.