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]

 

[_cytop]

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

 

[Sebastian42]

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]

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]

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]

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]

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]

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]

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]

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]

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.