Commandline interfacing

  • Thread starter Thread starter Dan Neely
  • Start date Start date
D

Dan Neely

Is it possible to automate loading a spreadsheet and a macro, runnin
the macro on the sheet, and then saving the modified sheet so that i
could be done by a seperate program with no user intervention i
midprocess
 
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?
Click to expand...

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.
 
dude screw excel; use Access-- you can do whatever you want from the
commandline

(having a command line Macro launch VBA code for example)
 
(e-mail address removed) wrote...
dude screw excel; use Access-- you can do whatever you want from the
commandline
Click to expand...
....

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?!
 
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
 
(e-mail address removed) wrote...
but they can't call different macros from the command line like you can
with access right?
Click to expand...

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.
 
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
-
 
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
Click to expand...
....

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.
 
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
 
Harlan said:
(e-mail address removed) wrote...

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.
Click to expand...

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.
 
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
 
(e-mail address removed) wrote...
he asked for command line interface to excel
Click to expand...
....

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.
 
(e-mail address removed) wrote...
....
access is the worlds most popular db.. it is the worlds most powerful
database (until SQL 2005 comes out)
Click to expand...

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.
 
Back
Top