Close file by name?

[Alexial]

I've been working on a script for a little while and learning some things along the way. One of my little work projects is to create a script to close a file on a server left open by a SQL job. We know the job is done, so there's no need to warn any users about closing it.

This is what I have thus far, however it doesn't seem to work. I've been testing it on my local computer (that has vista) so, not sure if that's part of the issue.

Dim ServerName
Dim FileName

ServerName = "computer"
FileName = "C:\Text.xls"

MsgBox "File " & FileName & " located."

Set objConnection = GetObject("WinNT://" & ServerName & "/LanmanServer")
Set colSessions = objConnection.Sessions
Set colResources = objConnection.Resources
Set Shell = CreateObject("WScript.Shell")

'On Error Resume Next

For Each objResource in colResources
  Set objOffice = GetObject(,"Excel.Application")
  If objResource.path = FileName Then
    objOffice.DisplayAlerts = False
    Set WBook = objOffice.ActiveWorkbook
      WBook.Saved = True
      WBook.Close
    objOffice.DisplayAlerts = True
    objOffice.Quit
    WScript.Sleep 2000
  End If
Next

When I run it, it does nothing except return that it has located the file and gives no errors, so I'm not particularly sure how to fix it or where I might have gone wrong. Let me know if there is any simpler way to do this? Thanks

 

[Alexial]

Yea, I suppose that's true. I just thought the comparison was kind of.. I dunno. But, I'll look around the net for some code for that. I didn't even know you could do that with an excel file.

Thanks for the suggestion.

 

[NWBeaver]

You could try replacing:
Set objOffice = GetObject(,"Excel.Application")

With:
Set objOffice = GetObject("c:\test.xls").Application

This should find the instance of Excel with the specified filename.

 

[Alexial]

It has to have the file path in it I'm guessing? Hmm. That's a good suggestion too.

I gave it a try and that works great! I left 3 excel documents open and it closed just the one. Thanks very much!

 

[NWBeaver]

Yes, it seems to need the file path.

FilePath = "C:\test.xls"
Set objOffice = GetObject(FilePath).Application

You're welcome.