Command line switches.

Hello,

We receive a rather poorly formatted csv file from our courier company to
serve as the digital copy of our monthly bill. I wanted to import this data
into Access 2003 so that it is easier for users to search and read the data.
I have created an Excel 2003 workbook that is to be used as an interface
file for Access 2003. I have created a macro that pulls the data in to the
spreadsheet, deletes what shouldn't be there, formats the rest, adds some
column headers, and saves it file to a new file. In Access 2003 I have then
written a script that is triggered by a button to pull the data into a table
and do some other bits. I was wondering if it would be possible to lauch
Excel opening a scpecified file and running a specified macro. If so, would
it be best done in VBA or are there command-line switches for this? I
figured that if the command-line switches were out there then I could write
a quick bacth file that could be fired from Access 2003 before it imports
the data.

Is there a better way to automate Excel?

TIA,

Jarryd

You can't run a macro from the command line. You could write a
procedure called Auto_Open that will be executed when the file is
opened.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Jarryd" <Jarryd@youllneverknow.com> wrote in message
news:OyD8J5r9FHA.2832@TK2MSFTNGP14.phx.gbl...
> Hello,
>
> We receive a rather poorly formatted csv file from our courier
> company to serve as the digital copy of our monthly bill. I
> wanted to import this data into Access 2003 so that it is
> easier for users to search and read the data. I have created an
> Excel 2003 workbook that is to be used as an interface file for
> Access 2003. I have created a macro that pulls the data in to
> the spreadsheet, deletes what shouldn't be there, formats the
> rest, adds some column headers, and saves it file to a new
> file. In Access 2003 I have then written a script that is
> triggered by a button to pull the data into a table and do some
> other bits. I was wondering if it would be possible to lauch
> Excel opening a scpecified file and running a specified macro.
> If so, would it be best done in VBA or are there command-line
> switches for this? I figured that if the command-line switches
> were out there then I could write a quick bacth file that could
> be fired from Access 2003 before it imports the data.
>
> Is there a better way to automate Excel?
>
> TIA,
>
> Jarryd
>

Easily done once you overcome Excel's lack of command line switches using
Windows Script Host (WSH).

' Filename: excel.vbs
' Overcomes Excel inability to run macros from the command line
' Usage: [CScript | WScript] excel.vbs macro(s)
'
'open excel
Dim macro
Dim XLApp
Dim XLWkb
Set XLApp = CreateObject("Excel.Application")
xlapp.visible = true
xlapp.workbooks.add
xlapp.workbooks.open "C:\Program Files\Microsoft
Office\Office\Xlstart\personal.xls"
'
'now run macro
If wscript.arguments.count = 0 Then
'do nothing
Else
macro = "Personal.xls!" & WScript.Arguments.item(0)
xlapp.run macro
End If



"Jarryd" <Jarryd@youllneverknow.com> wrote in message
news:OyD8J5r9FHA.2832@TK2MSFTNGP14.phx.gbl...
> Hello,
>
> We receive a rather poorly formatted csv file from our courier company to
> serve as the digital copy of our monthly bill. I wanted to import this
data
> into Access 2003 so that it is easier for users to search and read the
data.
> I have created an Excel 2003 workbook that is to be used as an interface
> file for Access 2003. I have created a macro that pulls the data in to
the
> spreadsheet, deletes what shouldn't be there, formats the rest, adds some
> column headers, and saves it file to a new file. In Access 2003 I have
then
> written a script that is triggered by a button to pull the data into a
table
> and do some other bits. I was wondering if it would be possible to lauch
> Excel opening a scpecified file and running a specified macro. If so,
would
> it be best done in VBA or are there command-line switches for this? I
> figured that if the command-line switches were out there then I could
write
> a quick bacth file that could be fired from Access 2003 before it imports
> the data.
>
> Is there a better way to automate Excel?
>
> TIA,
>
> Jarryd
>
>

Hello,

I think I have managed to sort something out. I used Sub Workbook_Open.
And I have managed to get it to open the xls file using a batch command.
The only thing I can't do is get Access to wait for the batch to finish
before continuing with the rest of the procedure.

But I suppose that is a question for another forum.

Cheers,

Jarryd
"Jarryd" <Jarryd@youllneverknow.com> wrote in message
news:OyD8J5r9FHA.2832@TK2MSFTNGP14.phx.gbl...
> Hello,
>
> We receive a rather poorly formatted csv file from our courier company to
> serve as the digital copy of our monthly bill. I wanted to import this
> data into Access 2003 so that it is easier for users to search and read
> the data. I have created an Excel 2003 workbook that is to be used as an
> interface file for Access 2003. I have created a macro that pulls the
> data in to the spreadsheet, deletes what shouldn't be there, formats the
> rest, adds some column headers, and saves it file to a new file. In
> Access 2003 I have then written a script that is triggered by a button to
> pull the data into a table and do some other bits. I was wondering if it
> would be possible to lauch Excel opening a scpecified file and running a
> specified macro. If so, would it be best done in VBA or are there
> command-line switches for this? I figured that if the command-line
> switches were out there then I could write a quick bacth file that could
> be fired from Access 2003 before it imports the data.
>
> Is there a better way to automate Excel?
>
> TIA,
>
> Jarryd
>