Creating a .bat file that opens, pauses & closes MS Excel

[alexa]

I am a beginner programmer and have never created a .bat
file. Although I have "some" prior knowledge of VB.
I am in need of creating a bat file (or something
comparable) that I can put into Scheduler to run every
morning.

The idea is to open Excel (Excel is set up to open the
file automatically), pause for 30 seconds (an automatic
update happens during this time), save the file
(overwrite), and then close Excel.
I have not been successful finding switches for Excel
(start it, save and close). Any switches, examples or
ideas you can provide would be extremely helpful. Thanks,
Alexa

 

[Jake Marx]

Hi Alexa,

I would suggest doing all processing in the Excel workbook instead of trying
to control Excel via the command line.

To open an Excel workbook, you can just enter the full path/filename for the
file into your batch file.

In the Workbook_Open event subroutine for your workbook, you can call a
subroutine to do what you need to do. At the end of the subroutine, do
this:

ThisWorkbook.Save
Application.Quit

In order for this to work unattended, you'll either have to:

1) set your macro security to low (not recommended)

or

2) sign the workbook with selfcert.exe (available on the MS site somewhere)
so it will be trusted

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Alexa Arnold]

Thanks Jake.

Is the subroutine created in Excel itself?

all of this (except for entering the full path/filename for the file
into the batch file) done in Excel itself?.

Thanks again.
Alexa

 

[Jake Marx]

Alexa,

Is the subroutine created in Excel itself?

Yes, I would put the code in the workbook you are opening. The
Workbook_Open event subroutine goes in the ThisWorkbook class module
(double-click it to get to its code pane). It could then in turn call
another subroutine (placed in a standard module) that does all the
processing. When done, the sub would save the workbook and quit Excel.

all of this (except for entering the full path/filename for the file
into the batch file) done in Excel itself?.

Yes.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Gord Dibben]

To add to Jake's advice........

You can use Windows Task Scheduler to start Excel with your workbook using the
fulpath\filename.

Self-cert.exe should be in your Office folder OR Start>Programs>Microsoft
Office>Microsoft Tools>Digital Certificates for VBA Projects.

Gord Dibben Excel MVP

 

[Toby Erkson]

To launch Excel from a batch file "file path of Excel.exe" "file path of Excel file to run"

For example:
rem Launch Excel example..
"C:\Program Files\Microsoft Office\Office10\EXCEL.EXE C:\temp\Excel-file.xls"
rem All done.

Use Excel to update, Save and Close itself given the previously mentioned help (Private Sub Workbook_Open()...End Sub). There are no useful switches for you in
Excel in this instance :-(
For a "sleep" command I use SLEEP.EXE (in the DOS section): http://www.computerhope.com/dutil.htm

Toby Erkson
Oregon, USA

 

[Alexa Arnold]

Toby,

Thanks so much! Extremely helpful. After a lot of time searching, I
have also learned that there are no useful switches which is what
prompted me to get out here and ask for help. Thanks again. I'll let
you know if it works.

Best regards,
Alexa

 

[Alexa Arnold]

Thanks Gord! I think with all of your (Jake, you and Troy) help. I will
be able to get this done.

Best regards,
Alexa

 

[Toby Erkson]

My job is to automate reporting. The stock Windows scheduler is extremely lacking in my opinion. If you are doing more automation stuff then I *highly*
recommend either scheduler from Arcana Dev elopement, http://www.arcanadev.com/ . I began using their Arcana Scheduler (back when it first came out) and am now
using adTempus (Scheduling Edition)...lovin' it.

I did look at Opalis, http://www.opalis.com/ , and originally wanted to use them first but I received absolutely NO HELP from them at all on their demo version.
adTempus was helpful and easy to use and I have no regrets.

Toby Erkson
Oregon, USA

 

[tristanm81]

I know absolutely nothing about writing batch files...

Is it possible to write a simple one that opens a workbook, and then
runs a specific macro ? If so , what would it look like?

thanks,

TNM

 

[Guest]

Just put the following in your batch file, including the quotes:

"c:\documentpath\filename"

where 'documentpath' is the file's path and 'filename' is, well, you get the
idea.
Windows knows to open ".xls" files in Excel.

In the workbook, insert your code module/sub/whatever, then insert a call to
the macro in the Workbook_Open() Event (in the PersonalWorkbook module)

-EW

 

[Dave Peterson]

How about a .vbs file?

Save this text file as somename.VBS

Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "c:\yourpathto\yourfile.xls"
xlapp.run "yourfile.xls!macronamehere"





Change the path and filename to what you need.

When you double click on this .vbs file, excel will start, the file will open
and your macro will execute.