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

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

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!

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]

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

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

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!

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!

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

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

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

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

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

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

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

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