How to close file from vbs?

  • Thread starter Thread starter D. Pirate Roberts
  • Start date Start date
D

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?
 
activeworkbook.close

or

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

or
Workbooks("test").Close SaveChanges:=True
 
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...
 
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...
 
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...
 
Oops, I meant to reply to JHP. Yes, I'd be very interested to know your
solution, JHP.

Thanks...
 
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
 
On error resume next
set bk = Workbooks("Text.xls")
bk.close Savechanges:=False
On Error goto 0
 
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
 
Back
Top