Re: How to close file from vbs? by D
D
Thu Oct 06 12:28:41 CDT 2005
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" <goawayspam@GFY.com> wrote in message
news:%23vTHxQpyFHA.3124@TK2MSFTNGP12.phx.gbl...
> 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" <dreadpirateroberts@bride.com> wrote in message
> news:em0W0woyFHA.3700@TK2MSFTNGP10.phx.gbl...
>> Oops, I meant to reply to JHP. Yes, I'd be very interested to know your
>> solution, JHP.
>>
>> Thanks...
>>
>> "JHP" <goawayspam@GFY.com> wrote in message
>> news:e2$h6IoyFHA.908@tk2msftngp13.phx.gbl...
>>> 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" <GKeramidas@msn.com> wrote in message
>>> news:ebPGd5gyFHA.2932@TK2MSFTNGP10.phx.gbl...
>>>> activeworkbook.close
>>>>
>>>> or
>>>>
>>>> Workbooks("test").Close SaveChanges:=False
>>>>
>>>> or
>>>> Workbooks("test").Close SaveChanges:=True
>>>>
>>>>
>>>>
>>>> --
>>>>
>>>>
>>>> Gary
>>>>
>>>>
>>>> "D. Pirate Roberts" <dreadpirateroberts@bride.com> wrote in message
>>>> news:%23VrxfUeyFHA.3236@TK2MSFTNGP14.phx.gbl...
>>>>>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?
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>