How to close file from vbs?

[D. Pirate Roberts]

I know there must be a simple way to do this but so far I haven't found it.
I have a vbs file that writes output to an Excel file. All I want to do is
have the script close the file if it is open when the script is run.
Something like:

If test.xls is open
Close test.xls

Is this possible?

 

[Gary Keramidas]

activeworkbook.close

or

Workbooks("test").Close SaveChanges:=False

or
Workbooks("test").Close SaveChanges:=True

 

[JHP]

Gary Keramidas,

I'm wondering if D. Pirate Roberts means to check if the file is in use
before running his script?

If this is the case I may have a solution for you - let me know...

 

[D. Pirate Roberts]

Gary,
Yes, I want to (1) check if the file is in use before running the script and
if it is then (2) close the file.

Thanks...

 

[D. Pirate Roberts]

Gary,
Yes, I want to (1) check if the file is in use before running the script and
if it is then (2) close the file.

Thanks...

 

[D. Pirate Roberts]

Oops, I meant to reply to JHP. Yes, I'd be very interested to know your
solution, JHP.

Thanks...

 

[JHP]

This is something that is limited in it's approach... it terminates all
opened Excel files - check the code and use what you like - if I come across
anything else I will post it here, or if you have anymore questions I will
try to help:

** watch for word wrap **

Option Explicit
On Error Resume Next

Dim strFile, objFSO, objFile, strComputer, objWMIService, colProcess,
objProcess
Dim strUserName, strDomainName
Const forAppending = 8
Const createFile = False

strFile = "C:\Macro.xls"
Set objFSO = CreateObject("Scripting.FileSystemObject")

If objFSO.FileExists(strFile) Then
Set objFile = objFSO.OpenTextFile(strFile, forAppending, createFile)

If Err.Number = 70 Then
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
Set colProcess = objWMIService.ExecQuery("SELECT * FROM Win32_Process")

For Each objProcess In colProcess
If objProcess.Name = "EXCEL.EXE" Then
objProcess.GetOwner strUserName, strDomainName
objProcess.Terminate()
WScript.Echo strUserName & " of " & strDomainName & " had " & strFile &
" in use"
End If
Next
Set colProcess = Nothing
Set objWMIService = Nothing
Else
objFile.Close
Set objFile = Nothing
WScript.Echo strFile & " is not in use"
End If
End If
Set objFSO = Nothing

 

[Tom Ogilvy]

On error resume next
set bk = Workbooks("Text.xls")
bk.close Savechanges:=False
On Error goto 0

 

[D. Pirate Roberts]

It is clear now that there is no simple way to do this in vbs. However, I
found a workaround that is the cleanest and easiest to implement than any
I've found to date. For any who are interested here it is:

Set objShell = Wscript.CreateObject("Wscript.Shell")
Set objFSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set objFile = objFSO.OpenTextFile("Test.xls", FORAPPENDING, FailIfNotExist,
OpenAsASCII)
objFile.Close
If Err.number<>0 Then
'Test.xls is already open and must be closed
MsgBox "Test.xls is already open. Please close it and try running the
script again.",vbExclamation,"Close File!"
wscript.quit(1)
End If
On Error goto 0