Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runing Excel macros from a Bat file.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runing Excel macros from a Bat file.
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 "NHRunner" wrote: | 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. | | | | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runing Excel macros from a Bat file.
Remove the line;
set oSheet = Nothing -- Regards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runing Excel macros from a Bat file.
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 "Dave Patrick" wrote in message ... 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 "NHRunner" wrote: | 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. | | | | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runing Excel macros from a Bat file.
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 "NHRunner" wrote: | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel '00, 1st file has macros, how to stop macro in 2nd file? | Excel Discussion (Misc queries) | |||
Runing two macros triggered by a button | Excel Discussion (Misc queries) | |||
Runing two macros triggered by a button | Excel Discussion (Misc queries) | |||
save excel file from a table delimited file (.txt) using macros | New Users to Excel | |||
VB code still runing | Excel Programming |