Need Macro to Open CSV File, Graph, Save File

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
JFraser
Posts: 2
Joined: Tue Aug 03, 2010 5:20 pm

Need Macro to Open CSV File, Graph, Save File

Post by 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
OpenOffice 3.2 on Windows XP
rudolfo
Volunteer
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Need Macro to Open CSV File, Graph, Save File

Post by 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 http://user.services.openoffice.org/en/ ... 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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Macro to Open CSV File, Graph, Save File

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
JFraser
Posts: 2
Joined: Tue Aug 03, 2010 5:20 pm

Re: Need Macro to Open CSV File, Graph, Save File

Post by 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?
OpenOffice 3.2 on Windows XP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Need Macro to Open CSV File, Graph, Save File

Post by 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply