How to close file from vbs?
 

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?

How to close file from vbs?
 

activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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...

"JHP" wrote in message
...
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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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

Thanks...

"JHP" wrote in message
...
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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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

"D. Pirate Roberts" wrote in message
...
Oops, I meant to reply to JHP. Yes, I'd be very interested to know your
solution, JHP.

Thanks...

"JHP" wrote in message
...
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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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

--
Regards,
Tom Ogilvy


"D. Pirate Roberts" wrote in message
...
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...

"JHP" wrote in message
...
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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?

How to close file from vbs?
 

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


"JHP" wrote in message
...
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

"D. Pirate Roberts" wrote in message
...
Oops, I meant to reply to JHP. Yes, I'd be very interested to know your
solution, JHP.

Thanks...

"JHP" wrote in message
...
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 Keramidas" wrote in message
...
activeworkbook.close

or

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

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



--


Gary


"D. Pirate Roberts" wrote in message
...
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?