Commandline interfacing

[Dan Neely]

Is it possible to automate loading a spreadsheet and a macro, running the macro on the sheet, and then saving the modified sheet so that it could be done by a seperate program with no user intervention in midprocess?

[Harlan Grove]

Dan Neely wrote...
>Is it possible to automate loading a spreadsheet and a macro, running
>the macro on the sheet, and then saving the modified sheet so that it
>could be done by a seperate program with no user intervention in
>midprocess?

You mean other than by using, say, VBScript to create an Excel
application instance and do this via Automation?

If the workbook has either a Workbook_Open event handler or an
Auto_Open macro, all you need to do is open the workbook with
macros enabled in Excel for either (or both) of these to run.
Put the statements

ThisWorkbook.Save
Application.DisplayAlerts = False
Application.Quit

at the end of either, and either will save the workbook then end
the Excel session after running the preceding code.

[[email protected]]

dude screw excel; use Access-- you can do whatever you want from the
commandline

(having a command line Macro launch VBA code for example)

[Harlan Grove]

[email protected] wrote...
>dude screw excel; use Access-- you can do whatever you want from the
>commandline
....

Kinda depends on what the OP wants to do, doesn't it?

And if Workbook_Open or Auto_Open are written properly, they can do
anything needed too, and all the OP would need to do to run the file
would be to issue a console command like

start /min d:\work\myfile.xls

This is difficult?!

[[email protected]]

but they can't call different macros from the command line like you can
with access right?

i just claim that excel is for babies

-aaron

[Harlan Grove]

[email protected] wrote...
>but they can't call different macros from the command line like you can
>with access right?

True. Spreadsheets aren't really intended to be used for batch
processing. Neither are word processors of internet browsers, but that
doesn't mean all these are superior to DBMSs for their *intended*
tasks.

Unless the OP wants to perform a database task, there's probably a
better way to accomplish the task than using either Excel or Access,
but offering Perl or WSH code would be a bit too OT for me.

[Andy Wiggins]

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
http://www.bygsoftware.com/Excel/VBA/vbs_script.htm--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Dan Neely" <[email protected]> wrote
in message news:[email protected]...
>
> Is it possible to automate loading a spreadsheet and a macro, running
> the macro on the sheet, and then saving the modified sheet so that it
> could be done by a seperate program with no user intervention in
> midprocess?
>
>
> --
> Dan Neely
> ------------------------------------------------------------------------
> Dan Neely's Profile:
http://www.excelforum.com/member.php...o&userid=24161
> View this thread: http://www.excelforum.com/showthread...hreadid=377873
>

[Harlan Grove]

Andy Wiggins wrote...
>Here is a sample VBS script. Save it in a standard text file with a .VBS
>extension.
....
>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
....

Maybe safer to insert

xlObj.EnableEvents = False

as the second statement. Further, more robust to set .DisplayAlerts to
FALSE than set .Saved to TRUE. Finally, no need for .ActiveWindow.Close
since the next statement ends the Excel session.

But to approximate Access commandline functionality, you'd need to
parse the script's commandline, pulling off the first argument as the
filename, the second as the macro name, and the rest as arguments to
the macro. Not particularly simple.

[[email protected]]

dipstick

he asked for command line interface to excel

stop using baby programs and use Access
you can call macros from the command line easily

[Dan Neely]

[email protected] wrote...
>but they can't call different macros from the command line like you can
>with access right?

True. Spreadsheets aren't really intended to be used for batch
processing. Neither are word processors of internet browsers, but that
doesn't mean all these are superior to DBMSs for their *intended*
tasks.

Unless the OP wants to perform a database task, there's probably a
better way to accomplish the task than using either Excel or Access,
but offering Perl or WSH code would be a bit too OT for me.

A real DB (ie not accces) app is the end goal, but rewriting the legacy code is a lower priority than new features, and for where user intervention's needed using excel's a simpler solution than trying to create all the needed features in a datagrid so some of it will probably be retained in the final product.

PS Thanks to the people who actaully provided useful replies.

[[email protected]]

hey screw you

access is the worlds most popular db.. it is the worlds most powerful
database (until SQL 2005 comes out)

you're crazy go play with excel little kid

[Harlan Grove]

[email protected] wrote...
>he asked for command line interface to excel
....

No, moron, he asked, "Is it possible to automate loading a spreadsheet
and a macro, running the macro on the sheet, and then saving the
modified sheet so that it could be done by a seperate program with no
user intervention in midprocess?"

That can be done without a commandline interface. If the same macro
would always be run, it can and should be made Workbook_Open or
Auto_Open, then no commandline interface required. It could also be
done by passing the macro name via an environment variable and writing
either Workbook_Open or Auto_Open to fetch the value of the environment
variable and then use Application.Run to call that macro.

[Harlan Grove]

[email protected] wrote...
....
>access is the worlds most popular db.. it is the worlds most powerful
>database (until SQL 2005 comes out)

Access may be the most widely sold, but unlikely it's the most often
used. As for powerful, you're confusing Access itself with all the
additional stuff Microsoft provides in theory. Access on its own is a
toy database. A rather sophisticated toy, often a useful toy, but a toy
nevertheless.