Hi --
  I want to be able to run excel in batch mode passing in the name of a file to be uploaded automatically into the spreadsheet.

The command would look like:

excel workbook.xls source_file.txt

Can this be done? How do I get the name of the text file and any other input arguments I might want to use?

Thanks,

Barry :)

Barry,

I find your question very confusing.
Do I have to understand that you want to load an text file automatically
when you open an certain excel file or not.
If you start excel then excel itself looks the XLSTART folder and if it finds
any workbooks then they wil be loeaded automatically.
In that file you could add some code in the WorkbookOpen routine.

The question is not confusing at all.
Let us assume that you want to open each time a new file. You will not want to change each time the WorkbookOpen routine. Or do you?

Any way here is a way how to do that in Excel 97.
I could not find a solution for Excel 2000 or heigher. If some one finds a solution please reply.

http://www.j-walk.com/ss/excel/eee/eee002.txt

Best Regards
sidibou

Interesting content of the link above:

POWER PROGRAMMING TECHNIQUE

By Laurent Longre

There is a way for an auto-start macro to read the arguments on the
command line (with Excel 97).

Assume that you want to read the command line argumentsfrom an Auto_open
sub in the workbook "c:\temp\test.xls" opened by a batch file (or by a
Win95 shortcut).

1. Your command line should look like this one:

start excel c:\temp\test /e/param1/param2/.../paramN

i.e. : after excel.exe, the name of the workbook containing the
Auto_open, then the switch /e **immediately** followed by your own
arguments. These arguments should be separated by "/" and form a
continuous string without spaces.

For instance, if you want to pass the arguments "c:\temp\file1.dbf",
"all" and "exclusive" to Excel, your command-line should look like:

start excel c:\temp\test /e/c:\temp\file1.dbf/all/exclusive

2. In Test.xls, use the API function GetCommandLine (alias
GetCommandLineA in Win95) to get the contents of this command-line
string.

You should then parse the string returned by GetCommandLineA, search for
the separators "/" and store each argument in an array. Here is an
example of a such Auto_open sub:


Option Base 1
Declare Function GetCommandLineA Lib "Kernel32" () As String

Sub Auto_open()

  Dim CmdLine As String 'command-line string
  Dim Args() As String 'array for storing the parameters
  Dim ArgCount As Integer 'number of parameters
  Dim Pos1 As Integer, Pos2 As Integer

  CmdLine = GetCommandLineA 'get the cmd-line string
  On Error Resume Next 'for the wksht-function "Search"
  Pos1 = WorksheetFunction.Search("/", CmdLine, 1) + 1 'search "/e"
  Pos1 = WorksheetFunction.Search("/", CmdLine, Pos1) + 1 '1st param

  Do While Err = 0
    Pos2 = WorksheetFunction.Search("/", CmdLine, Pos1)
    ArgCount = ArgCount + 1
    ReDim Preserve Args(ArgCount)
    Args(ArgCount) = Mid(CmdLine, Pos1, _
      IIf(Err, Len(CmdLine), Pos2) - Pos1)
    MsgBox "Argument " & ArgCount & " : " & Args(ArgCount)
    Pos1 = Pos2 + 1
  Loop

End Sub


If you use the command-line above, this Auto_open sub will automatically
store the three arguments ("c:\temp\file1.dbf", "all" and "exclusive")
in the Args() array and display them.

Again, be sure that you don't insert any space between /e and each
argument in the command-line, otherwise it could fail (Excel can believe
that these "pseudo-arguments" are the names of workbooks to open at
startup...).

I have been searching for several days for this solution. I'm working in Excel 2010, but was having trouble with Excel understanding the parameters I was sending. It kept interpreting the parameters as a file name, just as sidibou said in his reply:

"Again, be sure that you don't insert any space between /e and each
argument in the command-line, otherwise it could fail (Excel can believe
that these "pseudo-arguments" are the names of workbooks to open at
startup...). "

and kept trying to open nonexistent files and providing error messages to that effect!

Once I restructured my execution string, and modified my VBA code, I no longer got the error message!

Hard to believe a solution from 2005 is still relevant today.

I'm so glad I found this forum!