Page 1 of 2
close in a batch file
Posted: 23 Apr 2011 14:31
by greenspank
I would like to have the ability to open/close an excel file with a batch file.
Example of what I think it should do...
Code: Select all
@echo off
start C:\Users\Desktop\book1.xlsx
pause
pause
pause
close C:\Users\Desktop\book1.xlsx save=true
end
I know this is not right, so I'll leave it to someone that knows what he/she is doing...
Thanks for the help!
Re: close in a batch file
Posted: 23 Apr 2011 17:12
by nitt
greenspank wrote:I would like to have the ability to open/close an excel file with a batch file.
Example of what I think it should do...
Code: Select all
@echo off
start C:\Users\Desktop\book1.xlsx
pause
pause
pause
close C:\Users\Desktop\book1.xlsx save=true
end
I know this is not right, so I'll leave it to someone that knows what he/she is doing...
Thanks for the help!
I think this is what you are looking for:
Re: close in a batch file
Posted: 24 Apr 2011 08:06
by greenspank
Almost! Is there a way to make it save without asking to save before the sheet closes?
Re: close in a batch file
Posted: 24 Apr 2011 12:12
by nitt
greenspank wrote:Almost! Is there a way to make it save without asking to save before the sheet closes?
Nooo. START is just a shell the loads the application. TASKKILL just kills the tasks. The prompt and the worksheet aren't connected in any ways. You'd need VBScript to do this.
Re: close in a batch file
Posted: 24 Apr 2011 15:29
by greenspank
yeah.. i figured that. Thing is this sheet is on a network and the people that open it will never "Enable Macros" because they act like tards...
I could lower the security settings... but the "real IT people" that run the network will invoke a policy to raise it once the pc is rebooted. Gotta love it...
Re: close in a batch file
Posted: 25 Apr 2011 08:36
by aGerman
Well, in my opinion VBScript is also no option. As far as I understand it you need to observe the Workbook_BeforeClose - event. This can be done in VBA but only if macros are enabled...
The only thing you could do using VBScript is something like that:
*.vbs
Code: Select all
'''' Define the file full name.
strWBFullName = "C:\Users\Desktop\book1.xlsx"
'''' Check whether the file exists.
If Not CreateObject("Scripting.FileSystemObject").FileExists(strWBFullName) Then
MsgBox "File """ & strWBFullName & """ does not exist or is not available.", vbCritical, "File Not Found"
WScript.Quit
End If
'''' Create a new Excel-instance, display it and open the file.
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
Set oWB = oXL.Workbooks.Open(strWBFullName)
'''' Do something useful ;-)
oWB.Worksheets(1).Select
oWB.Worksheets(1).Cells(1, 1).Select
oWB.Worksheets(1).Cells(1, 1).Value = "Hello World!"
'''' Let the user notice what we did.
WScript.Sleep 5000
'''' Save the workbook and clean up.
oWB.Save
oWB.Close
oXL.Quit
Set oXL = Nothing
Regards
aGerman
Re: close in a batch file
Posted: 28 Apr 2011 22:45
by shajanjp
If taskkill command is not working, you can also use PStools for doing the same...
Re: close in a batch file
Posted: 17 May 2011 13:42
by Ed Dyreen
I know i'm responding to an old thread...
Something that will always work, a language designed to do all those things you thought weren't possible.
http://www.autoitscript.com/site/autoit/
Re: close in a batch file
Posted: 18 May 2011 01:38
by orange_batch
He could possibly write the script in batch as is, and use a simple vbscript to do the keypress for prompts possibly on a delay. The batch could also do checks on running processes for stability.
Re: close in a batch file
Posted: 18 May 2011 08:14
by Ed Dyreen
@orange_batch
VBSCRIPT for keypresses, i hope that is a joke
Tell me, can you press a specific button in a specific window, while the user is actively using it's pc ?
Moving the window, hiding a window, opening another window with the same name ?
I don't think so, no really AutoIT is the way to go !
Re: close in a batch file
Posted: 18 May 2011 12:45
by jeb
Ed Dyreen wrote:@orange_batch
VBSCRIPT for keypresses, i hope that is a joke
Tell me, can you press a specific button in a specific window, while the user is actively using it's pc ?
Moving the window, hiding a window, opening another window with the same name ?
Hmm, there is no problem at all if you use choice.exe, but it isn't present at XP.
With VBScript/Jscript it can be done with a trick (It's annoying that I didn't discoverd it
).
Even with batch the same trick can be used (I have only a problem to close the keyboard-process).
Both solutions use the XCOPY command for the key input.
try this and you got the idea
jeb
Re: close in a batch file
Posted: 18 May 2011 12:48
by Ed Dyreen
What is special about that ? it just says :
Code: Select all
xcopy 1 1 /L /P
File not found - 1
0 file(s)
C:\
Hmm, there is no problem at all if you use choice.exe, but it isn't present at XP.
Is present, i've downloaded it.
Both solutions use the XCOPY command for the key input.
How get key input ? You mean from file ?
You make me look like a nooob
Re: close in a batch file
Posted: 18 May 2011 13:17
by jeb
You aren't a noob, it's a heavy trick.
I forgot to say, that you need a file (in this case "1")
Then you got these lines typing "qwertz"
Code: Select all
C:1 (J/N)? q
C:1 (J/N)? w
C:1 (J/N)? e
C:1 (J/N)? r
C:1 (J/N)? t
C:1 (J/N)? z
C:1 (J/N)?
You didn't need to press enter to get a new line, and that is important
create a file with the current pressed keys, and as it contains newlines it is created "on the fly".
So another process can parse the file to detect the latest pressed key.
To start a new task in the same window you can use vbscript or simply
or
Code: Select all
call "%~f0" :task2 | call "%~f0" :task1
As a pipe starts each part in his own task.
The trick is build by Tom Lavedas, his code is (from
alt.msdos.batch.nt)
Code: Select all
@if (@X)==(@Y) @goto :Dummy @end/* Batch part
@echo off
set "Input="&setlocal&set delay=3000 %/ milliseconds /%
set _d=>%temp%.\tmp1&set _d=>%temp%.\tmp2
set /p Input=Enter key here: < nul
for /f "delims=" %%I in (
'cscript //nologo //e:jscript "%~f0" %delay%'
) do endlocal & set "Input=%%I"
del %temp%.\tmp?.
echo.
echo.For example: "%Input%"
goto :EOF
Jscript part */
var con=new ActiveXObject("Scripting.filesystemobject")
.opentextfile("con",2);
var d=WSH.Arguments(0),t=0,s='\x01';
var cmd="xcopy %temp%.\\tmp1 %temp%.\\tmp2 /p /y<con";
var sh=new ActiveXObject("WScript.Shell");
with(sh){with(Exec("%comspec% /c"+cmd)){
while(s=='\x01'){s='\x01';
WSH.Sleep(240);t+=250;
sh.AppActivate(ProcessID);sh.sendkeys("^A");
while(s!='?')s=StdOut.Read(1);StdOut.Read(1);
s=StdOut.Read(1);if(t>d){s='Timeout';break}}
sh.AppActivate(ProcessID);sh.sendkeys("n")}}
con.Write(s); // shows keyed character [optional]
WSH.Echo(s)
jeb
Re: close in a batch file
Posted: 18 May 2011 13:50
by Ed Dyreen
I don't get the point, What does it enables us to do?
are you refering to my previos comment ?
a specific button in a specific window
100% fail proof, I mean that the key Is NOT send to the wrong WindowHandle &ButtonID
I have to admit, I know little about VBscript &less about Jscript which is quite a problem it seems to be
Re: close in a batch file
Posted: 18 May 2011 13:56
by jeb
It's an replacement/improvement for the choice command, it can handle more keys than choice and it doesn't need a timeout.
It works in the current cmd-window and catches the keys, but the main batch can run asynchronous.
jeb