Delete file with vbscript

[davidam]

The follwing vbscript now works fine except that it will not delete the file as instructed on the second to last line. Would appreciate any suggestions. (By the way the wscript.sleep line seems to be the only way to get it not to repeat the If-then part several times over...this seems to be more of a problem when monitoring a subfolder than a folder on the c drive) I would appreciate any suggestions on that as well. Many thanks.

Dim xlApp
Dim bk1
Dim bk2
Dim sh1
Dim sh2
Set xlApp = CreateObject("Excel.Application")
strComputer = "."
strDirToMonitor = "C:\\\\Gather\\\\Receive"
strTime = "5"
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _
    ("SELECT * FROM __InstanceOperationEvent WITHIN " & strTime & " WHERE " _
        & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _
            & "TargetInstance.GroupComponent= " _
                & "'Win32_Directory.Name=" & Chr(34) & strDirToMonitor & Chr(34) & "'")
 Wscript.Echo vbCrlf & Now & vbTab & _
 "Begin Monitoring for a Folder Change Event..." & vbCrlf
 Do While True
  Set objLatestEvent = colMonitoredEvents.NextEvent
   If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then
 
       xlApp.visible = true
        Set bk1 = xlApp.Workbooks.Open("c:\Gather\Receive\Transfile2.xls")
        Set bk2 = xlApp.Workbooks.Open("C:\Test.xls")
        Set sh1 = bk1.Worksheets("Sheet1")
        Set sh2 = bk2.Worksheets("Sheet1")
         wscript.Sleep 1200
           sh1.Activate
           sh1.Range("A1:N3").Copy
           sh2.Activate
           sh2.Range("A6").Select
           sh2.Paste
            bk1.Close False
            bk1.Delete
     End If
 Loop

 

[ColinKJ]

Hi davidam,

Kill ("c:\Gather\Receive\Transfile2.xls")

Should work.

ColinKJ

 

[AlphaFrog]

I think the reason your If statement is endlessly looping is because of
Do While True

Do while what is True? You need something like...
Do While something = True


To delete the file, try something like this...
Kill c:\Gather\Receive\Transfile2.xls"

 

[davidam]

The Kill statement did not work. It crashes Windows Script Host like my previous delete statement. I think it might have something to do with the fact that the file is now a variable?

 

[davidam]

I tried resetting the bk1 variable:

    Set bk1 = xlApp.Workbooks.Open("c:\Gather\Receive\Hold.xls")
        Kill "c:\Gather\Receive\Transfile2.xls"

Same result

 

[xenou]

Maybe you need to disassociate the object from the reference variable (?).

Try (after closing the workbook):

Set bk1 = Nothing
Kill "c:\Gather\Receive\Transfile2.xls"

Then see if you can Kill it. Kill is a vb/vbscript command so it should work in your vbs script as is.

On an unrelated note, you are opening the other book but also not closing it, or saving. Re-opening an unsaved workbook typically results in discarding the changes. Be sure you are careful with all of these objects: Set the references, Open, make desired changes, Close and/or Quit, and release references (whether in the loop or outside of it, as the situation requires).