Macro to make 1,200 Barcharts?

[elmerruffingspoon]

hello everyone,

I have 100 excel files exported from spss, each containing 12 sets of data (a factor with 6 sub-factors, all listed in column A) for 6 demographic factors, mean scores between 1 and 5 in the other columns.

I'll take the first row from each of the 12 sets to create 12 colored barcharts showing each demographic profile which I'll later insert into a powerpoint (100 of those too!).

Is there a way to use an Excel macro or an automatic method to avoid doing this 12 times for 100 files?

Thanks!

Elmer

 

[Hui]

Elmer

Firstly, Welcome to the Chandoo.org Forums

Can you please post two sample files of the 100

Highlight the fields that should be charted

What type of chart ?
What Headings for the Axis ? or is that clear from the Data
What title on the charts?

 

[elmerruffingspoon]

Hello Hui,

Thanks for your reply.

I attach a sample as you recommend. I attach only 1 file because the 100 tables are all identical - except for the values. I highlighted in yellow the rows I have to make charts for: that's 13 charts for each table...

For your questions:

I want to make a simple barchart: BUT I want each column in the barchart to be a different color, unlike the sample I made in the table attached where I'm stuck with all columns being one same color!

The desired headings/chart legends are highlighted in orange (top of the table).

The title should be the yellow highlighted text in column A ("Q1", "Q2" etc).

I really want to learn how to do this, thanks so much for any ideas, suggestions, samples etc.!

Kindly,

E.

 

[Hui]

Put the below file into the same directory as the source files
Press the Button "Make Charts"
Sit back and enjoy


I would recommend that you try this on say 3 files first
Copy 3 of the source files into a temporary directory
Then put the Master_Charter file into the same directory

The macro assumes that the source files are all the same format

Do you realise there is no Question 8 ?

Notice the Donate Button below!

 

[elmerruffingspoon]

Wow Hui,

That's amazing. I did a first test and I'll do some more tomorrow and let you know how it goes.. in case I have any things to clarify of adjust. I'll definitely Donate ;-)!!

Thanks again,

E.

 

[Hui]

How did we go E?

 

[elmerruffingspoon]

I was really impressed by your jedi skills and even more so by your generous spirit to help an excel newbie in need! Your help worked - I did some tests - and in the end decided to make each chart by hand so I could have total control over each product. But now I 'know how to fish'. Thanks again and I know where to look for help next time I need it.

E