Run macro from command line

[Alan Wilson]

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

 

[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

"Alan Wilson" <[email protected]>
wrote in message
news:[email protected]...

 

[Alan Wilson]

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]

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" <[email protected]>
wrote in message

 

[Alan Wilson]

Nick
I'm not familiar with VBA: is there anyone you could recommend who
could pay to write the necessary code & detail how to embed it?
Thanks
(e-mail address removed)

 

[NickHK]

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