close in a batch file

[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!

[nitt]

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:

Code: Select all

taskkill /f /im excel.exe > nul

[greenspank]

Almost! Is there a way to make it save without asking to save before the sheet closes?

[nitt]

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.

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

[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

[shajanjp]

If taskkill command is not working, you can also use PStools for doing the same...

[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/

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

[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 !

[jeb]

@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

Code: Select all

xcopy 1 1 /L /P

jeb

[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

[jeb]

You aren't a noob, it's a heavy trick.

I forgot to say, that you need a file (in this case "1")

Code: Select all

xopy 1 1 /L /P

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

Code: Select all

xopy 1 1 /L /P > keyInput.tmp

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

Code: Select all

start /b myBatch.bat

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

[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

[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