For DOS batch file

[Sebastian42]

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]

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]

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]

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]

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]

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]

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

 

[xenou]

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]

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]

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")
'msgbox x.caption
x.SaveAs "C:\FileLocation\WorkBookName.XLS" (<- Yes, I know the application object does not have a .SaveAs method)
x.Application.Quit
Set x= Nothing

But that does not take into account multiple instances of Excel, neither can it find an open workbook or anything else...

Difficult to comment further unless there was a little more context as regards what you're doing.

If you need it, more information about VBS here

I would guess that Sebastian42 on MrExcel is SmileyMan here... if so please update all your posts if this question is resolved

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]

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.