# For DOS batch file



## Sebastian42 (Oct 4, 2010)

What VBS script would cause an open Excel sheet to be saved ?

[I'm confused by the VBA in the 'title' : *Excel Questions* *All Excel/VBA questions]*


----------



## xenou (Oct 4, 2010)

There's not usually any difficulty saving a file  ... hit the save button ...


----------



## Sebastian42 (Oct 5, 2010)

I agree it is not difficult to save by Control S, but it is one more step which I have to do routinely, that I'd like to be  automated, along with closing Excel and backing up the saved file. The latter two steps are already taken care of in a (DOS) batch file, and if it is possible, I'd like to include the Saving as well. I have been given VBS script for it, but I don't know how to incorporate that into a DOS Batch File. It's been suggested that it is an API rather than an Excel issue, but I have not found any relevant API forum that I can ask.


----------



## _cytop (Oct 5, 2010)

Just to continue from the cross post at OzGrid...

"My" code was simply a demonstration of what could be done... not a complete working solution. Plus, the 'language' (as such) is VB script not a DOS batch file. 

Copying that code into a batch file will result in errors simply because it is being processed as a batch file and not a VBS file. GetObject, for example, is not a valid internal (external, operable program or batch file) command in DOS. 

As regards approaching API experts for your solution, I would have been quite willing to help you to develop an VBS/API based solution if that would have been a suitable approach but your emphasis on 'my' code not working was a little off-putting. 

Just out of interest, I am a C++/C#, Assembler programmer, oh, and I've used that horrible Visual Basic extensively in all versions from Visual Studio 2008 all the way back to Visual Basic Version 1.0 in 1991 - so I have a reasonable knowedge of the Windows API). I hang around the Ozgrid board to gain an appreciation of the Excel user functionality like formulas and stuff like that.


----------



## Sebastian42 (Oct 5, 2010)

Cytop
I am sorry you were put off by the factual statement that your code  did not achieve my aim in the environment for which I want it. I seem to be an expert at unintentionally getting on the wrong side of the moderators/members (?) of these two/three excel forums.

By the way, I have tried to find the warnings about cross-posting in a signature, but am at a loss as to how to go about that.

I thought forums were about helping people who do not yet know, but here I feel penalised for my ignorance. I do not know if a VBS/API solution is what I need. 

In a different matter I have been able to run VBS script in a DOS batch file quite successfully, and was hoping that might be the case for SAVING AN OPEN EXCEL WORKBOOK too. I would rather incorporate whatever solution is available in a DOS batch file, than to start over and have to do those three things (save, close, backup) in a different way that I am not familar with.

If your tolerance has not yet been exhausted, I'd appreciate your VBS/API solution to try in a batch file.


----------



## _cytop (Oct 5, 2010)

The 'issues' about cross posting are explained more eloquently here that any thing I could write...

Forums are about helping - and learning. I learn a lot about the 'visible' (user interface wise) aspects of Excel from the boards. In return I share what I do know about the coding aspects.

I don't have a ready made solution to your problem. It's not something I have done in that exact context. But I do know how to find a Window, find it's child windows and then find a command button (or menu item) in that window and execute the click event for that control. but I'm not sure it's the most practical solution to your issue as it's a lot of work for relatively little return. 

Basically, it's an overcomplicated solution for a simple problem; What happens in 6 months or a year? Applications get upgraded... Class names might change, or even the menu caption changes. Perhaps Sebastian42/SmileyMan gets promoted...


----------



## _cytop (Oct 5, 2010)

... and if you want a flavour of what you're getting into, read this post


----------



## Sebastian42 (Oct 5, 2010)

It does look quite daunting, and maybe not worth the trouble.  Thank you for your effort.
p.s.  promotion is not likely to affect my asthma, the recording and charting of which (of peakflow anyway) is the content of the SS whose use I was trying to 'minimise'.


----------



## Joe4 (Oct 5, 2010)

> By the way, I have tried to find the warnings about cross-posting in a signature, but am at a loss as to how to go about that.


We allow cross-posting, we just ask that you extend the courtesy to include a link to the other threads you have on the topic in other forums so that others can see what has already been done on the subject.

Our policy on cross-posting can be seen in rule #24 here:


> 24. Cross-posting is the term we use when a person has posted the same question to multiple online forums. While there is nothing actually "wrong" with this, it is asked that you at least mention that the question has been posted elsewhere, including a link to the thread on the other forum. There are many people who post at several different forums; threads are cross-posted without stating it are easily identified and pointed out as such.
> *Read: http://www.excelguru.ca/node/7*


----------



## RoryA (Oct 5, 2010)

FYI, you cannot directly use API calls from VBS anyway.
However, assuming you know the name and path of the file, you can simply use GetObject to grab a reference to it, and from that you can save it, save a copy of it elsewhere as a backup and/or generally do what you like.


----------



## Sebastian42 (Oct 4, 2010)

What VBS script would cause an open Excel sheet to be saved ?

[I'm confused by the VBA in the 'title' : *Excel Questions* *All Excel/VBA questions]*


----------



## _cytop (Oct 5, 2010)

Didn't want to really scare Sebastian - so no mention of COM classes,  DLL's,  C and other nasty things like that


----------



## Sebastian42 (Oct 5, 2010)

Rorya
That does seem to be the solution I want - provided I can execute it in a DOS batch file. 
Can you refer me to a model/template which I can modify/adapt to my specifics ?


----------



## RoryA (Oct 6, 2010)

Sure, if you can specify the steps you want taken, we can probably do it all in a vbscript that you just call from the batch file.


----------



## Sebastian42 (Oct 6, 2010)

I just want the open excel workbook saved.
I already have a batchfile that does the rest of what I want.
I would expect the batchfile to first execute your code, and then do the rest that I already have 'code' for.


----------



## Sebastian42 (Oct 6, 2010)

A short time after posting, I wanted to add this 'the below', but I could not find an edit option.
If what you are asking for is the total content of the batchfile, then the answer is : 
1. save the workbook 2.close excel 3. backup the file to the slave drive.


----------



## RoryA (Oct 6, 2010)

Something like this (note: it assumes you only have one Excel instance open):

```
dim objWbk, objXL
set objXL = getobject(, "Excel.Application")
if not objXL is nothing then
 on error resume next
 set objWbk = objxl.workbooks("ADO Source.xls")
 if not objWbk is nothing then
 objwbk.Save
 objwbk.close false
 end if
end if
```


----------



## Sebastian42 (Oct 6, 2010)

I pasted that code into a batch file, and the expected happened when I double clicked the batfile. A DOS window opened with a series of error messages as follows :
C:\Desktop>if not objXL is nothing then
Syntax error
C:\Desktop> on error resume next
Bad command or file name
C:\Desktop> set objWbk = objxl.workbooks("ADO Source.xls")
C:\Desktop> if not objWbk is nothing then
Syntax error
C:\Desktop> objwbk.Save
Bad command or file name
C:\Desktop> objwbk.close false
Bad command or file name
C:\Desktop> end if
Bad command or file name
C:\Desktop>end if
Bad command or file name

I have no doubt that in the correct context, your code will do what I wish for, but it seems that the ability to 'call the code from a batch file' is in question. I suspect that Temp.VBS is required, but where and what it contains I have no idea. And there is probably other requirements as well.
.


----------



## Sebastian42 (Oct 6, 2010)

I was prevented from entering the following in edit mode because I had delayed too long :

In a totally different application, I have been able to successfully run VBS code in a DOS batchfile. That batchfile starts with 
ECHO. Set Ws=WScript.CreateObject("WScript.Shell") >_TEMP.VBS
then follows code that obviously refers to the 'different application'
and then the batchfile ends with 
:: Run the VBS script
  cscript //nologo _TEMP.VBS
  :: Now delete VBS temporary script file
  DEL _TEMP.VBS

When I started and ended the excel-saving batchfile the same way, and sandwiched 'your code' in between, the batchfile still triggered those error messages.


----------



## Sebastian42 (Oct 6, 2010)

Cytop
I have just seen your post about not scaring me with COM classes, DLLs and C - you are right, I would have felt 'at sea'. Classes sounds like Java to me, I KNOW DLL means Dynamic Link Library, and I have newphew who claims to have worked on developing C.


----------



## RoryA (Oct 6, 2010)

The code I posted is VBS - it does not go in a batch file. You add it to a text file and save it as a VBS file. (after editing the name of the Excel workbook as required)
Then call that file from your batch file using the cscript command and passing the full path to the .vbs file.


----------



## Sebastian42 (Oct 4, 2010)

What VBS script would cause an open Excel sheet to be saved ?

[I'm confused by the VBA in the 'title' : *Excel Questions* *All Excel/VBA questions]*


----------



## Sebastian42 (Oct 6, 2010)

I feel on the verge of a breakthrough ! Yippee !

Rather than doing 'endless' experimenting, can you tell me if your code (after suitable substitutions) should be saved as C:\filename.vbs; and then a batch file is written with that first line I quoted at the top plus the  four lines at the end ? where '_TEMP.VBS' is replaced by 'filename.VBS' ?
Is  'using the cscript command and passing the full path to the .vbs file' rendered by 'cscript //c:\filename.vbs' ?
And for substitutions in the code : I don't know which terms to substitute 'readings.xls' for, is it just 'source.xls' and can the rest be left as is ?


----------



## RoryA (Oct 6, 2010)

Replace the "ADO SOurce.xls" part with "readings.xls" then save the vbs file as C:\filename.vbs. Then amend your batch file to use:

```
cscript //nologo C:\filename.vbs
```
You should not require that first line you quoted at all.


----------



## Sebastian42 (Oct 6, 2010)

It seemed to work the first time, but not again ! ? If it requires a reboot, that is not a probem, because I would only do it once a day anyway. But while I am adapting it to real filenames and real paths etc, I have to be able to keep checking that it works....


----------



## RoryA (Oct 6, 2010)

It should not require a reboot. It should work every time assuming you only have one Excel instance running and the file name hasn't changed.


----------



## Sebastian42 (Oct 6, 2010)

Worked like a charm, once I got rid of the bottom part of 'my' code, which deleted filename.vbs

Thank you SO much for staying the distance -  I hope you feel some of the satisfaction that I am aglow with - YOUR OWN satisfaction at lifting me from ignorance...


----------



## RoryA (Oct 6, 2010)

It *always* cheers me up to get a working solution - that's why I do this!


----------



## xenou (Oct 6, 2010)

To be fair to _cytop, it was pointed out as far back as post #4 not to put vbs commands in the dos script itself.  The remarks were spot on.


> Copying that code into a batch file will result in errors simply because it is being processed as a batch file and not a VBS file. GetObject, for example, is not a valid internal (external, operable program or batch file) command in DOS.


----------



## Sebastian42 (Oct 6, 2010)

I was well aware that VBS was not likely to work in a DOS batch file, but as that was the code given to me in the context of asking for something to add to my already existing batchfile, I felt I had to try it and report the results. 
I was looking for suggestions that WOULD work, rather than ones that would NOT.


----------



## _cytop (Oct 7, 2010)

What I suggested in the first place was NOT a working solution, but a suggestion as to how it might have been done.

After saying that, I'd like to quote my original answer to you on Ozgrid for comparison with Rorya's post above...



> Simplistically...
> 
> Dim x
> set x = getobject(, "Excel.application")
> ...


 
And as both RoryA and myself pointed out, you still have issues if there is more than one instance of Excel open... so to my mind your existing solution is not robust.


----------



## Sebastian42 (Oct 7, 2010)

Thank you for your concern about the non-robustness of the solution I have adopted. In one of my numerous postings somewhere, I did say that I want the ability to use a batchfile to SAVE as well as close and backup, an open spreadsheet - specifically the  one that I open to edit EVERY DAY to record the readings related to monitoring my asthma - and because it is EVERY DAY, I considered it worthwile to reduce the repetitive tasks involved (even if it is only the one extra step of saving). The code you presented, overwhelmed innocent-of-VBS-me. I recognise now that it is like the code I eventually used. If you look back (as you seem to do a lot) you'll read that the light went on for me only when it was stated categorically that what I wanted to do in a batchfile was indeed possible. It may well be that I became more receptive after that - in any case, I was distracted by other issues - accusations of infractions which in hindsight I don't deny, but  at the time were like thunder from a blue sky. While I have received very valuable help from the fora, and deeply appreciate it, it is also true the fora are quite complex in the way they have to be negotiated - I might say a minefield for unsuspecting intruders.

You kindly gave me a reference about VBS, which I did lookup, but found daunting as a newbie to VBS, rather than helpful in guiding me to my own solution.

I am quite happy with the performance of the batchfile I now have, and do not expect to use it in any other context - specifically, I will not expect it to handle multiple instances of excel. 

Once again, I thank you for the interest you have shown in my question and the effort you keep putting into dealing with my issues.


----------



## Sebastian42 (Oct 4, 2010)

What VBS script would cause an open Excel sheet to be saved ?

[I'm confused by the VBA in the 'title' : *Excel Questions* *All Excel/VBA questions]*


----------



## Sebastian42 (Oct 14, 2010)

Thank you. With the help of the other Excel forums, I have in fact achieved what I wanted : A line in a batch file to "call" a VBS script :

cscript //nologo C:\path\xlfile.VBS


and the contents of xlfile.VBS :

dim objWbk, objXL
set objXL = getobject(, "Excel.Application")
if not objXL is nothing then
 on error resume next
 set objWbk = objxl.workbooks("xlfile.xls")
 if not objWbk is nothing then
 objwbk.Save
 objwbk.close false
 end if
end if


----------

