Manipulate Excel workbooks programmatically

tags:

excel-2007

views:

228

answers:

Manipulate Excel workbooks programmatically

I have an Excel workbook that I want to use as a template. It has several worksheets setup, one that produces the pretty graphs and summarizes the numbers. Sheet 1 needs to be populated with data that is generated by another program. The data comes in a tab delimited file.

Currently the user imports the tab delimited file into a new Workbook, selects all and copies. Then goes to the template and pastes the data into sheet1.

This is a large amount of data, 269 columns and over 135,000 rows. It’s a cumbersome process and the users are not experienced Excel users. All they really want is the pretty graphs.

I would like to add a step after the program that generates the data to programmatically automate the process the user currently must do manually.

Can anyone suggest the best method/programming language that could accomplish this?

You can can solve this, for example, by a simple VBA macro. Just use the macro recorder to record the steps the user does manually now, this will give you something to start with (you probably will have to add a function to let the user choose the import file).

You said you have some data generated by another program. What kind of program? A program that you have developed by yourself and where you can add the excel-import functionality? Or a third party program with a GUI that cannot be automated easily?

And if you really want to create an external program for this task - choose whatever programming lanuguage you like as long as it can use COM objects. In .NET, you have the option of using VSTO, but I would only suggest that for this task if you have already some experience with that (but than you would not ask this kind of question, I think :-))

Doc Brown 2010-03-25 23:00:19

The data comes from a couple of programs that the engineering department has created. We use CA’s workload automation to create job control applications. I was planning to email the zipped output file and have the user manually perform the Excel import procedure. Between the fact that the users don’t like the process and the file is often too large to email, I want a solution where I can just send the chart worksheet from the workbook. The macro would make it easier for the user but it doesn’t address the large files. Thanks for your suggestion.

Tom 2010-03-26 18:42:07

Look here:

Create Excel (.XLS and .XLSX) file from C#

There's NPOI (.NET Framework version of POI) so that you can code in C# if you want.

Leniel Macaferi 2010-03-25 23:19:22

Thanks for the suggestion but I don’t have a .NET development environment and like most businesses these days I’d be hard pressed to get them to spend money on new software licenses.

Tom 2010-03-26 18:44:04

POI is the answer. Look at the Apache website. You can use java to read the data and place it in cells. The examples are very easy.

No One in Particular 2010-03-25 23:26:31

I’ve downloaded the POI modules from Apache and I’m hoping I’ll be able to get that to work.Thanks for the suggestion.

Tom 2010-03-26 18:44:48

Tom, post here again if you have questions. I will check back.

No One in Particular 2010-03-27 02:13:53

ansaurus

tags:

views:

answers:

Manipulate Excel workbooks programmatically

related questions