Run macro from command line

Alan Wilson · #1 08-22-2006, 05:57 AM

I can open a tab-separated file automatically from the command line, eg process_create("excel.exe","file.txt"), but then I want to Save As an Excel file: I can create a macro to do that, but how can I run the macro automatically, on the command line?
Any help appreciated!

#2 08-22-2006, 06:25 AM

Alan,
What is "process_create" ? Similar to Shell ?

You could specify a workbook that has a suitable routine in its
Workbook_Open event instaed of the "file.txt".
And there are ways to read the command line to Excel so you can pass more
parameters, e.g. the text file to open, the .SaveAs file name etc.

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As
String

But still need a macro in the WB open to do the saving.

Or use a VBScript + Automation

NickHK

"Alan Wilson" <Alan.Wilson.2cxoq2_1156237205.3892@excelforum-nospam.com>
wrote in message
news:Alan.Wilson.2cxoq2_1156237205.3892@excelforum-nospam.com...
>
> I can open a tab-separated file automatically from the command line, eg
> process_create("excel.exe","file.txt"), but then I want to Save As an
> Excel file: I can create a macro to do that, but how can I run the
> macro automatically, on the command line?
> Any help appreciated!
>
>
> --
> Alan Wilson
> ------------------------------------------------------------------------
> Alan Wilson's Profile:
http://www.excelforum.com/member.php...o&userid=37858
> View this thread: http://www.excelforum.com/showthread...hreadid=574090
>

Alan Wilson · #3 08-22-2006, 06:51 AM

Nick

Yes, process_create() is similar to Shell.
If I can pass the filename to SaveAs as a parameter in the command line, then perhaps a macro would not be needed: how could I do this? Is there a definitive list of command line options somewhere?

Thanks
Alan

[quote=NickHK]Alan,
What is "process_create" ? Similar to Shell ?

You could specify a workbook that has a suitable routine in its
Workbook_Open event instaed of the "file.txt".
And there are ways to read the command line to Excel so you can pass more
parameters, e.g. the text file to open, the .SaveAs file name etc.

Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA" () As
String

But still need a macro in the WB open to do the saving.

Or use a VBScript + Automation

NickHK

#4 08-22-2006, 07:15 AM

Alan,
You can control Excel from the command line like that as there is nothing to
interpret the arguments.
Here's the standard command line switches available:
http://support.microsoft.com/default.aspx?kbid=291288

So you will need a macro or VBS/Automation to control Excel.
(Or you could use DDE if your desperate)

NickHK

"Alan Wilson" <Alan.Wilson.2cxr9r_1156240506.8037@excelforum-nospam.com>
wrote in message
news:Alan.Wilson.2cxr9r_1156240506.8037@excelforum-nospam.com...
>
> Nick
>
> Yes, process_create() is similar to Shell.
> If I can pass the filename to SaveAs as a parameter in the command
> line, then perhaps a macro would not be needed: how could I do this? Is
> there a definitive list of command line options somewhere?
>
> Thanks
> Alan
>
> NickHK Wrote:
> > Alan,
> > What is "process_create" ? Similar to Shell ?
> >
> > You could specify a workbook that has a suitable routine in its
> > Workbook_Open event instaed of the "file.txt".
> > And there are ways to read the command line to Excel so you can pass
> > more
> > parameters, e.g. the text file to open, the .SaveAs file name etc.
> >
> > Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineA"
> > () As
> > String
> >
> > But still need a macro in the WB open to do the saving.
> >
> > Or use a VBScript + Automation
> >
> > NickHK
>
>
> --
> Alan Wilson
> ------------------------------------------------------------------------
> Alan Wilson's Profile:
http://www.excelforum.com/member.php...o&userid=37858
> View this thread: http://www.excelforum.com/showthread...hreadid=574090
>

Alan Wilson · #5 08-22-2006, 07:29 AM

Nick
I'm not familiar with VBA: is there anyone you could recommend who I could pay to write the necessary code & detail how to embed it?
Thanks
alanwilson42@compuserve.com

#6 08-22-2006, 11:35 AM

Alan,
Here's some basic code to see if this is what you mean.
You need to:
1 - Open Excel with a new workbook.
2 - Press Alt + F11
3 - You will looking at the VBA IDE. You should see a window with a tree
structure something like "VBAProject (Book1.xls)" with "Sheet1 (Sheet1)" etc
underneath and "ThisWorkbook" at the bottom
4 - Double this last entry, to open its code pane.
5 - Paste in the code below
6 - Close the file and save somewhere suitable.
7 - Double click the file to run the code. Depending on your security
setting, you may be asked if you want to enable macro; click Yes.
8 - Select the text file when asked in the dialog. The Excel file will be
saved to the same folder as the text file with the same name.

NickHK

'--------- Code
Option Explicit

Private Sub Workbook_Open()
Dim RetVal As Variant
Dim WB As Workbook

RetVal = Application.GetOpenFilename("TextFiles (*.txt),*.txt", , "Select
the text file to process", , False)
If RetVal = False Then Exit Sub

Set WB = Workbooks.Open(RetVal)
RetVal = Left(RetVal, InStr(1, RetVal, ".") - 1)

With WB
.SaveAs RetVal & ".xls", xlNormal
.Close
End With

'Application.Quit

End Sub
'--------- End Code

"Alan Wilson" <Alan.Wilson.2cxsw2_1156242608.2604@excelforum-nospam.com> ¼¶¼g©ó¶l¥ó·s»D:Alan.Wilson.2cxsw2_1156242608.2604@excelforum-nospam.com...
>
> Nick
> I'm not familiar with VBA: is there anyone you could recommend who I
> could pay to write the necessary code & detail how to embed it?
> Thanks
> alanwilson42@compuserve.com
>
>
> --
> Alan Wilson
> ------------------------------------------------------------------------
> Alan Wilson's Profile:
> http://www.excelforum.com/member.php...o&userid=37858
> View this thread: http://www.excelforum.com/showthread...hreadid=574090
>

MynameisKamath · #7 08-04-2009, 01:16 AM

Hi,
Say if u want to open a run a Macro called "MacroName" from the CMD prompt then u can do the following. All the u need to do is to ADD a new function called "Auto_Open"

Private Sub Auto_Open()
MacroName
End Sub

Then enter on the command line:
c:\MyexcelSS.xls

This should execute the macro.

Regards
Kamath

Welcome to the Excel Forum

Forum Statistics