Runing Excel macros from a Bat file.

N

NHRunner

I've seen pieces of answers to pieces of this question but I've never seen a
complete answer that works. I can get close but there is always a problem
at the end. I thought I had done all of this kind of stuff myself over the
years and when a friend told me he couldn't get it working I just sent him
off examples of code that where the pieces I'd used. But in the end I'd
never used them exactly as stated below so my "do this and do that" memory
of what effects what just fell on its face.

Here's the complete flow needed.

Using the windows task scheduler:

A bat file is run

Excel is called and the "Auto_Open" macro is run.

Various VBA code does its thing, which does not include updating anything in
the workbook or does not include any kinds of prompt, dialog box. The VBA
code just reads and writes texts files and does nothing that would normally
require that an object or variable be set to NOTHING. The proper opens and
closes are done for the file numbers. Very vanilla code.

The workbook needs to close and that seems to happen.

Excel needs to quit, close, go away, gone-already, but it doesn't.

This is where I've seen dozens of "try this" suggestions but in the end
Excel doesn't quit, it just sits there with its grey panel with no workbook
open.

Ideally this whole process should run minimized as well.

Thanks for considering the challenge.
 
D

Dave Patrick

Try scheduling this vbscript something like;

cscript C:\mypath\myscript.vbs

---------------------
Option Explicit
Dim filePath, oExcel

filePath = "c:\Test.xls"
Set oExcel = CreateObject("Excel.Application")
oExcel.Workbooks.Open(filepath)
oExcel.Run "macro1"
oExcel.ActiveWorkbook.Save
oExcel.ActiveWorkbook.Close
oExcel.Quit
set oSheet = Nothing
Set oExcel = Nothing
---------------------

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| I've seen pieces of answers to pieces of this question but I've never seen
a
| complete answer that works. I can get close but there is always a problem
| at the end. I thought I had done all of this kind of stuff myself over the
| years and when a friend told me he couldn't get it working I just sent him
| off examples of code that where the pieces I'd used. But in the end I'd
| never used them exactly as stated below so my "do this and do that" memory
| of what effects what just fell on its face.
|
| Here's the complete flow needed.
|
| Using the windows task scheduler:
|
| A bat file is run
|
| Excel is called and the "Auto_Open" macro is run.
|
| Various VBA code does its thing, which does not include updating anything
in
| the workbook or does not include any kinds of prompt, dialog box. The VBA
| code just reads and writes texts files and does nothing that would
normally
| require that an object or variable be set to NOTHING. The proper opens and
| closes are done for the file numbers. Very vanilla code.
|
| The workbook needs to close and that seems to happen.
|
| Excel needs to quit, close, go away, gone-already, but it doesn't.
|
| This is where I've seen dozens of "try this" suggestions but in the end
| Excel doesn't quit, it just sits there with its grey panel with no
workbook
| open.
|
| Ideally this whole process should run minimized as well.
|
| Thanks for considering the challenge.
|
|
|
|
|
|
 
N

NHRunner

Other than one hiccup that works great. Thank you very much.

The hiccup was that it crashed near the end with a VBS error "oSheet" not
defined.

I just defined osheet and the error went away, but could I just have removed
"> set oSheet = Nothing"
or is there some function that provides that isn't clear looking at the
code.

Also, was the "oExcel.ActiveWorkbook.Save" necessary if there is nothing
changed in the workbook.

regards
Steve
 
D

Dave Patrick

Yep, remove that line.
You can replace;

oExcel.ActiveWorkbook.Close

with

oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.Close False

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Other than one hiccup that works great. Thank you very much.
|
| The hiccup was that it crashed near the end with a VBS error "oSheet" not
| defined.
|
| I just defined osheet and the error went away, but could I just have
removed
| "> set oSheet = Nothing"
| or is there some function that provides that isn't clear looking at the
| code.
|
| Also, was the "oExcel.ActiveWorkbook.Save" necessary if there is nothing
| changed in the workbook.
|
| regards
| Steve
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top