|
Creating a .bat file that opens, pauses & closes MS Excel
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 |
Creating a .bat file that opens, pauses & closes MS Excel
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 wrote: 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 |
Creating a .bat file that opens, pauses & closes MS Excel
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Creating a .bat file that opens, pauses & closes MS Excel
Alexa,
Alexa Arnold wrote: 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] |
Creating a .bat file that opens, pauses & closes MS Excel
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 StartProgramsMicrosoft OfficeMicrosoft ToolsDigital Certificates for VBA Projects. Gord Dibben Excel MVP On Thu, 29 Apr 2004 15:34:15 -0700, "Jake Marx" wrote: 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 |
Creating a .bat file that opens, pauses & closes MS Excel
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 On Thu, 29 Apr 2004 15:11:28 -0700, "alexa" wrote: 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 |
Creating a .bat file that opens, pauses & closes MS Excel
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Creating a .bat file that opens, pauses & closes MS Excel
Thanks Gord! I think with all of your (Jake, you and Troy) help. I will be able to get this done. Best regards, Alexa *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Creating a .bat file that opens, pauses & closes MS Excel
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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
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. tristanm81 wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 -- Dave Peterson |
Creating a .bat file that opens, pauses & closes MS Excel
How about use of the "startup" options?
But I can not get this to work... It works in WORD. Any .bat file gurus out there? call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mMYMACRO MYFILE.xls "Eric White" wrote: 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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
Here is a sample VBS script. Save it in a standard text file with a .VBS
extension. This example: a.. creates an Excel object, b.. opens a workbook, c.. sets a value, d.. runs a VBA routine, e.. then tidies up and closes down. Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls" xlObj.Range("NoOfCharsPerLine").Value = 50 xlObj.Run "ReadFromTextFile" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Noe" wrote in message ... How about use of the "startup" options? But I can not get this to work... It works in WORD. Any .bat file gurus out there? call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mMYMACRO MYFILE.xls "Eric White" wrote: 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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
Here is what I created:
Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "MYWORKBOOK.xls" xlObj.Run "PERSONAL.XLS!MYMACRO" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit But it did not "take/work"...I did not see EXCEL open/run... I realy like the batfile approach, because I can check Retrun Codes... "Andy Wiggins" wrote: Here is a sample VBS script. Save it in a standard text file with a .VBS extension. This example: a.. creates an Excel object, b.. opens a workbook, c.. sets a value, d.. runs a VBA routine, e.. then tidies up and closes down. Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls" xlObj.Range("NoOfCharsPerLine").Value = 50 xlObj.Run "ReadFromTextFile" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Noe" wrote in message ... How about use of the "startup" options? But I can not get this to work... It works in WORD. Any .bat file gurus out there? call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mMYMACRO MYFILE.xls "Eric White" wrote: 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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
To run the VBS you must have Windows Scripting Host (WSH) installed.
see: MSDN Online: Windows Scripting http://msdn.microsoft.com/library/de...scriptinga.asp -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Noe" wrote in message ... Here is what I created: Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "MYWORKBOOK.xls" xlObj.Run "PERSONAL.XLS!MYMACRO" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit But it did not "take/work"...I did not see EXCEL open/run... I realy like the batfile approach, because I can check Retrun Codes... "Andy Wiggins" wrote: Here is a sample VBS script. Save it in a standard text file with a .VBS extension. This example: a.. creates an Excel object, b.. opens a workbook, c.. sets a value, d.. runs a VBA routine, e.. then tidies up and closes down. Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls" xlObj.Range("NoOfCharsPerLine").Value = 50 xlObj.Run "ReadFromTextFile" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Noe" wrote in message ... How about use of the "startup" options? But I can not get this to work... It works in WORD. Any .bat file gurus out there? call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mMYMACRO MYFILE.xls "Eric White" wrote: 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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 |
Creating a .bat file that opens, pauses & closes MS Excel
You can make it visible by adding this line.
xlobj.visible = true But you'll have to load your personal.xls workbook in the script. Here's a thread with a few options: http://groups.google.co.uk/group/mic...4b901d22c9b77d or http://snipurl.com/h4zw Noe wrote: Here is what I created: Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "MYWORKBOOK.xls" xlObj.Run "PERSONAL.XLS!MYMACRO" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit But it did not "take/work"...I did not see EXCEL open/run... I realy like the batfile approach, because I can check Retrun Codes... "Andy Wiggins" wrote: Here is a sample VBS script. Save it in a standard text file with a .VBS extension. This example: a.. creates an Excel object, b.. opens a workbook, c.. sets a value, d.. runs a VBA routine, e.. then tidies up and closes down. Set xlObj = CreateObject("Excel.application") xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls" xlObj.Range("NoOfCharsPerLine").Value = 50 xlObj.Run "ReadFromTextFile" xlObj.ActiveWorkbook.Saved = True xlObj.ActiveWindow.Close xlObj.Quit -- Andy Wiggins FCCA www.BygSoftware.com Excel, Access and VBA Consultancy - "Noe" wrote in message ... How about use of the "startup" options? But I can not get this to work... It works in WORD. Any .bat file gurus out there? call "C:\Program Files\Microsoft Office\OFFICE11\excel.exe" /mMYMACRO MYFILE.xls "Eric White" wrote: 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 "tristanm81" wrote: 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 -- tristanm81 ------------------------------------------------------------------------ tristanm81's Profile: http://www.excelforum.com/member.php...o&userid=22702 View this thread: http://www.excelforum.com/showthread...hreadid=214801 -- Dave Peterson |
| All times are GMT +1. The time now is 04:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com