Excel Charting using VBA

[ncp218]

Hi,

I have been lurking for quite a while and picking up bits of code which have proved very useful - Thanks For That.

I now have a bit of a problem that I need assistance with.

We have a datalogger which collects streams of data that we then need to process and present on an Excel chart.

The data comes to us in a *.csv file format which we then sort as necessary to get the correct orientation and convert the file to an *.xls file (using a Macro). Each file then has a unique file name which is also the sheet tab.

I then have a macro which puts all the sheets in a folder into a single workbook. The sheet names are generally a02-pp or similar (in case it makes any difference).

What I then do at the moment is to copy the template chart into the spreadsheet behind each sheet of data and then change the source data to match the sheet that I need.

The data on the sheet has:-
ColA - Dist (m)
ColB - x
ColC - y
ColD - x
we plot the three variables x,y and z on the the y axis against the values for Dist to give a three line x,y graph.

What I would like to do is to be able to copy in the sheet with the chart on it, change the chart name to match the data it comes from and the change the data source to match the appropriate sheet. The data source is generally:-

='b01-pp'!$A$1:$D$3250

where 'b01-pp' is the data set I am interested in

Any ideas or hints? - To do it manually does not take long for a single plot but I have at least 390 charts to generate in this instance.

Also does anyone know how to generate a waterfall plot using lines with each set of data offset by a certain number?

Thanks in anticipation.

Nigel

 

[Sal Paradise]

Here's a page on waterfall charts:
http://peltiertech.com/Excel/Charts/Waterfall.html

The first question someone else may help you with.