Newbie : question on VB Script

[Daniel]

Hi everybody,

After many trials and errors I finaly wrote a VB script which works (see
below), to automatically run Excel and open a specific worksheet at startup.
This worksheet contains an Auto_open macro that performs a few tasks.

Now, I would like to add some code in my VB script to automatically save
that worksheet and close Excel.

How could I do that ? Whatever I tried failed.

Many tks in adavance for your time and kind help.

With best regards,
Daniel

Here is my actual script :
---------------------------------
Wscript.Sleep (10000)

Set objShell = WScript.CreateObject("WScript.Shell")

objShell.Run """Excel.exe"" ""C:\Documents and Settings\My
documents...\MyFile.xls"""

Wscript.Sleep (10000) 'délai de 10 secondes avant de fermer

[Code to save worksheet and close Excel]
-----------------------------------

 

[createwindow]

Dear Daniel,

Try something like this simple sample which saves under a different
name (so as not to destroy your original!

Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("Excel.Application")
Set objWorkBook = objExcel.Workbooks.Open("C:\mytest.xls")
objWorkBook.SaveAs ("C:\mytest2.xls")
objWorkBook.Close True
Set objWorkBook = Nothing
Set objExcel = Nothing

Google for Excel.Application for more samples on cell manipulation
etc.


Hope that helps.

CreateWindow
http://mymessagetaker.com
The while-you-were-out message program you have been looking for!

 

[Gord Dibben]

You state "open a worksheet".

I assume you mean "workbook" since worksheets reside in workbooks.

You could incorporate the workbook save and close in the auto_open code.

Sub auto_open()

'run your code to perform a few tasks

ActiveWorkbook.Save
Application.Quit
End Sub


Gord Dibben MS Excel MVP

 

[Daniel]

Hello createwindow.

thank you so much for your time and help. I will try this code and let you
know.
best regards,
Daniel

 

[Daniel]

Hi Gord.

thank you for your help.
I will try this now and post the result.

with my brgds,
Daniel

You state "open a worksheet".

I assume you mean "workbook" since worksheets reside in workbooks.

You could incorporate the workbook save and close in the auto_open code.

Sub auto_open()

'run your code to perform a few tasks

ActiveWorkbook.Save
Application.Quit
End Sub


Gord Dibben MS Excel MVP

Hi everybody,

After many trials and errors I finaly wrote a VB script which works (see
below), to automatically run Excel and open a specific worksheet at
startup.
This worksheet contains an Auto_open macro that performs a few tasks.

Now, I would like to add some code in my VB script to automatically save
that worksheet and close Excel.

How could I do that ? Whatever I tried failed.

Many tks in adavance for your time and kind help.

With best regards,
Daniel

Here is my actual script :
---------------------------------
Wscript.Sleep (10000)

Set objShell = WScript.CreateObject("WScript.Shell")

objShell.Run """Excel.exe"" ""C:\Documents and Settings\My
documents...\MyFile.xls"""

Wscript.Sleep (10000) 'délai de 10 secondes avant de fermer

[Code to save worksheet and close Excel]

 

[Daniel]

Hello again, Gordon.

Piece of cake. I did what u suggested and it works.
I simply added an "Application.wait" instruction before closing, to see what
was happening with my macro.
Now I will add a checkbox to give the choice between "Auto close" or
"carry-on working" with the spread sheet.

many tks again for your time and kind help.
Brgds,
Daniel

You state "open a worksheet".

I assume you mean "workbook" since worksheets reside in workbooks.

You could incorporate the workbook save and close in the auto_open code.

Sub auto_open()

'run your code to perform a few tasks

ActiveWorkbook.Save
Application.Quit
End Sub


Gord Dibben MS Excel MVP

Hi everybody,

After many trials and errors I finaly wrote a VB script which works (see
below), to automatically run Excel and open a specific worksheet at
startup.
This worksheet contains an Auto_open macro that performs a few tasks.

Now, I would like to add some code in my VB script to automatically save
that worksheet and close Excel.

How could I do that ? Whatever I tried failed.

Many tks in adavance for your time and kind help.

With best regards,
Daniel

Here is my actual script :
---------------------------------
Wscript.Sleep (10000)

Set objShell = WScript.CreateObject("WScript.Shell")

objShell.Run """Excel.exe"" ""C:\Documents and Settings\My
documents...\MyFile.xls"""

Wscript.Sleep (10000) 'délai de 10 secondes avant de fermer

[Code to save worksheet and close Excel]

 

[Newbie Coder]

Daniel,

Correct newsgroup:

microsoft.public.scripting.vbscript