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

 

[NWBeaver]

It appears to me that the first problem is that the message box will always pop up saying it has found the file, even if there is no such file. It appears to be simply echoing the filename, without ever actually looking for it...

 

[Alexial]

Yea, I just realized that a little while after I posted it. Not counting that though, I am not sure what's making this NOT work unless it can't locate the file.

 

[NWBeaver]

Try inserting something like this in your code to see if it actually finds the file:


Set objFSO = CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(FileName) then
MsgBox "File " & FileName & " located."
else
MsgBox "File " & FileName & " NOT located."
end if

 

[NWBeaver]

Another problem I found, is that objResource.path will not equal FileName, it will return the path to the Excel application itself, not the name of the open workbook.

Try using

objOffice.ActiveWorkBook.Name = FileName

with FileName = "test.xls

 

[NWBeaver]

My last post was kind of messed up, this is what I was trying to suggest:


FileName = "test.xls"
Set objOffice = GetObject(,"Excel.Application")
if objOffice.ActiveWorkbook.name = FileName then
'do stuff
end if

 

[tsuji]

Not sure if you know and that it does not perform or that you do not know the remove() method.
[tt]
For Each objResource in colResources
If strcomp(objResource.path,FileName,1)=0 Then
objResource.remove objResource.name
exit for
End If
Next
[/tt]

 

[Alexial]

Great thank you so much! I'll give the suggestions a try Just got back to work today after a nice thanksgiving weekend.

 

[Alexial]

Great!! Thanks NWBeaver, that worked like a charm The finished code might be a bit sloppy, but this works for me:

Dim ServerName
Dim FileName

ServerName = "computer" 'example
FileName = "Text.xls"   'example 

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

On Error Resume Next

For Each objResource in colResources
  Set objOffice = GetObject(,"Excel.Application")
  If objOffice.ActiveWorkBook.Name = 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

Thanks all for your help and patience!

 

[tsuji]

If there are several instances of excel application opened, how do you assure getobject(,"excel.application") get the reference to that particular workbook at the first instance?!!

 

[Alexial]

There shouldn't be more then one open at a time. The SQL job only opens this one. It has to be closed so it can be written to once it's copied. At least, that's the way our database guy explained it to me.

 

[EBGreen]

The Japanese would never attack the US. Or at least thats what the intelligence guy said.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Alexial]

Well. No need to be rude. The code works.

 

[Alexial]

I should say: It works, and it does exactly what he wants it to do. I already showed it to him and explained what it does.

So, Thank you for your help to those who did.

And as an added note, if you have a suggestions, say it; I'd be more then happy to flesh it out some more. If you don't have anything constructive to add, don't say anything.

 

[tsuji]

Is that what you call rude? Naivety is spectacular.

 

[Alexial]

Oh no, not you Tsujsi. Daijoubu desu. Arigato gosaimasu. : )

I hope you're japanese and not chinese, otherwise I doubt that sentence will make much sense.

 

[Alexial]

I was referring to what EBGreen had to say.

 

[EBGreen]

Ok, here is my suggestion. Have the at least look at something identifiable in the workbook (sheet names maybe) to be sure that you are closing a file that is more probably the right one.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Alexial]

Ok, well that's fine- And a good suggestion. I'll have a look at it later. I was just very put off at how you initially presented it.

From what I had been reading on the forums everyone seemed rather friendly, and for some reason it came across as an aggressive response.

I'll take a look into that later.

 

[EBGreen]

Just an allegory to demonstrate that you can never take users (or DBAs) at their word. Your code should always assume that input/supposed system state is wrong.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]