launching Excel with a macro to operate on CSV data?
 

Is it possible to launch Excel with command-line arguments so that it
imports a CSV file and then runs a macro on that file to format the data?

If not possible from the command line, what's the easiest way to do it
programmatically. I want to do this from a WIN32 SDK C program.
Currently, I just use ShellExecute on the CSV file to open it. That
works great. But of course, doesn't do any formatting.

Thanks,
Rob

launching Excel with a macro to operate on CSV data?
 

Rob Y wrote:
Is it possible to launch Excel with command-line arguments so that it
imports a CSV file and then runs a macro on that file to format the data?

If not possible from the command line, what's the easiest way to do it
programmatically. I want to do this from a WIN32 SDK C program.
Currently, I just use ShellExecute on the CSV file to open it. That
works great. But of course, doesn't do any formatting.

Thanks,
Rob

Just curious. There were responses posted to just about every other
question asked yesterday but this one. Am I completely off base in
wanting to do this? I've found some usenet postings from other people
trying to do the same exact thing - so it doesn't seem that outlandish.

If it's not possible to automate pre-processing a CSV file with a macro
from the command line, is it at least possible to do it via OLE
automation (or some other programmatic mechanism)? Can you get Excel to
import a CSV file and *then* run a macro on the resulting worksheet,
leaving the results open in Excel and detaching the calling program from
excel? If so, can somebody please point me to the appropriate API(s) to
use?

Thanks,
Rob

launching Excel with a macro to operate on CSV data?
 

Tim Williams wrote:
Not certain if you can use a command line argument for Excel but you can
certainly use COM automation to do what you need. From the command line you
could do this via a vbscript.

Google for "Excel automation": plenty of examples.


Thanks. I tried the vbscript example you pointed to, and it works (for
both CSV and XLS files - that's a relief).

Some more info. My application produces the CSV files. It's one of my
end users that wants the ability to automate running his macros on the
files (my job would just be to allow him to associate a particular macro
with a particular type of CSV file). Since I'm not going to be writing
the macros (or, I presume, vbscripts if that's what we go with), which
method do you think is most flexible. In other words, since I don't
know anything about the specific scripts or macros involved, I just want
to be able to substitute a CSV file path and either a vbscript or excel
macro file (or template xls) into a standardized process and have it work.

I don't know anything about vbscript or COM automation. My app is
written in C to the standard WIN32 SDK (does it not being in C++ make
COM a lot harder?). So, can either or both of those technologies handle
what I want to do? If so, would you suggest one over the other in this
scenario:

1. launch Excel
2. tell Excel to load a template XLS file containing the macro in question.
3. tell Excel to load a CSV file
4. tell Excel to run the macro (I guess I'd need a standard macro
function name to call?)
5. detach from Excel, leaving it up so the user can then play with the
resulting spreadsheet, and my app can go back to doing what it normally
does.

Thanks again,
Rob

launching Excel with a macro to operate on CSV data?
 

Well, I found some sample code that shows me how to do OLE automation of
Excel. The funny thing is that I can't for the life of me find any
documentation on what API's Excel offers for OLE automation. Even the
guy who wrote the sample code said that he figured out what calls to
make by recording macros and guessing based on the macro listings.

Is that weird or what? Maybe I'm just looking in the wrong places. Or
maybe there's a standard way to map from XLL API's (well documented) to
OLE-callable stuff.

Does anybody know where to find documentation and/or code samples?

Again, specifically, I want to

1. Launch Excel.
2. Load up a macro template.
3. Load up a csv data file.
4. run a macro in the template.

Sounds pretty straightforward, no?

Thanks,
Rob

launching Excel with a macro to operate on CSV data?
 

I'd do this in VBS:

Dim xlApp
Dim xlAddin
Dim xlWks

Set xlApp = CreateObject("Excel.application")
xlApp.Visible = True
Set xlAddin = xlApp.workbooks.Open("C:\youraddinnamehere.xla")

Set xlWks = xlApp.workbooks.Open("C:\yourcvsfilenamehere.csv") .worksheets(1)

xlApp.Run "'" & xlAddin.Name & "'!yourmacronamehere"

'and if you want to close things...
'xlWks.Parent.Close False 'cancel changes to .csv file
'xlAddin.Close False 'cancel changes to addin
'xlApp.Quit
'
'Set xlWks = Nothing
'Set xlAddin = Nothing
'Set xlApp = Nothing

======
ps. I'm assuming that a macro template is an addin.


Rob Y wrote:

Well, I found some sample code that shows me how to do OLE automation of
Excel. The funny thing is that I can't for the life of me find any
documentation on what API's Excel offers for OLE automation. Even the
guy who wrote the sample code said that he figured out what calls to
make by recording macros and guessing based on the macro listings.

Is that weird or what? Maybe I'm just looking in the wrong places. Or
maybe there's a standard way to map from XLL API's (well documented) to
OLE-callable stuff.

Does anybody know where to find documentation and/or code samples?

Again, specifically, I want to

1. Launch Excel.
2. Load up a macro template.
3. Load up a csv data file.
4. run a macro in the template.

Sounds pretty straightforward, no?

Thanks,
Rob

--

Dave Peterson