Need Macro to Open CSV File, Graph, Save File

[JFraser]

Hi,

I am new to macros and have a project that needs to be completed. I have found some useful code such as opening and saving, but I have a long ways to go.

The macro needs to:
1. Run on OO Calc open
2. Display dialog box to open csv file into sheet 2 (number of sample points will vary form file to file)
3. Graph data (x-y lines only) on sheet 1 using Y1 and Y2 as legend and inserting X, Y, and title names
4. Save to a new file (original file must be kept so the process can be repeated)

It would be nice to:
Import second set of csv data and plot on same graph as csv data 1 so the two can be compared
Have multiple y axis legends for Y1, Y2, etc..
Have a variable number of input columns (Y1, Y2, Y3, etc..)

Input Format
X,Y1,Y2
1,50,30
2,75,25
3,90,20

Thanks for your help

[rudolfo]

I guess you can mark your step 1 and 4 as done if you say you found code for opening and saving.
You may have a look at thread viewtopic.php?f=45&t=30963 to get an idea and some sample code for the graph/chart step.
Using a custom dialog box is not a easy thing if you start with OOo macros. I always decide to avoid the extra hassle and go for a poor man solution: Use a sheet on its own with named references for the 4 or 5 cells that I need to specify input parameters for the macro and read the content of these cells at the beginning of the macro.

I hope this keeps you going.

[Villeroy]

Much easier and more powerful than macros:

Create a data registered source pointing to a directory of similar csv files.
Similar= equal delimiters, encoding and name suffix.
[file>New>Database, connect to existing databse, type:Text, specify the csv-directory, store the database doc]

Create a Calc template, hit F4 drag some csv data (placeholder) from the left pane onto a cell.
menu:Data>Database Ranges...
Pick "Import1", button [More], check extra options "Keep formatting" and "Insert/Remove cells".
Finish with buttons [Modify] and [OK].
If you like, add some formulas adjacent to the import range (calculated fields to the right, summations below),
Add [conditional] formattings, charts, lookup tables, other links, whatever you need.
Store the template.
###################################################################
Next time:
Drop new csv files in the csv directory,
Open the Calc template,
Drag another csv-table from the datasource window onto the existing data range.
Everything (including adjacent formulas) will expand/shrink to the new size of the imported row set.

You do not need to edit the Base document anymore but in that document (more like a configuration file) you can define virtual tables (queries) where you can enforce filtered row sets and/or sort orders.

[JFraser]

Thanks!

The database method is much easier. The only problem Is the template does not automatically open the data source window and point to the right directory. Is there a way to fix this? Also, is there a way to password protect the calc template so that the end user can't change it?

[Villeroy]

Key F4 and 2 double-clicks should be affordable. If the file name is always the same and you always import the same file with new content, you can set options "Don't save import data" for the database range. This will prompt the user to refresh the import range, enforcing the range to be refreshed with the very same file but new content. So you would not even need to hit F4.

Try to unlock the import columns, then protect all sheets.