Newbie : question on VB Script

  • Thread starter Thread starter Daniel
  • Start date Start date
D

Daniel

Guest
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]
-----------------------------------
 
Re: Newbie : question on VB Script

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!
 
Re: Newbie : question on VB Script

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

On Sun, 4 Nov 2007 01:16:45 +0100, "Daniel" <notmyrealaddress@wanadoo.fr> wrote:

>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]
>-----------------------------------
>
 
Re: Newbie : question on VB Script

Hello createwindow.

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

<createwindow@gmail.com> a écrit dans le message de news:
1194136744.681360.56970@q3g2000prf.googlegroups.com...
> 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!
>
>
>
>
>
 
Re: Newbie : question on VB Script

Hi Gord.

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

with my brgds,
Daniel


"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de news:
4heqi31h0p1ebnqstjiont4252un7gadsq@4ax.com...
> 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
>
> On Sun, 4 Nov 2007 01:16:45 +0100, "Daniel" <notmyrealaddress@wanadoo.fr>
> wrote:
>
>>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]
>>-----------------------------------
>>
>
 
Re: Newbie : question on VB Script

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


"Gord Dibben" <gorddibbATshawDOTca> a écrit dans le message de news:
4heqi31h0p1ebnqstjiont4252un7gadsq@4ax.com...
> 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
>
> On Sun, 4 Nov 2007 01:16:45 +0100, "Daniel" <notmyrealaddress@wanadoo.fr>
> wrote:
>
>>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]
>>-----------------------------------
>>
>
 
Re: Newbie : question on VB Script

Daniel,

Correct newsgroup:

microsoft.public.scripting.vbscript

--
Newbie Coder
(It's just a name)


"Daniel" <notmyrealaddress@wanadoo.fr> wrote in message
news:u9gXJgnHIHA.1208@TK2MSFTNGP05.phx.gbl...
> 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]
> -----------------------------------
>
>
 
Back
Top