Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


EXCEL spreadsheets... Graphs and Automating! VB?

Posted on 2004-10-21
Medium Priority
Last Modified: 2010-04-17
I have over three hundred graphs that I need to create from an EXCEL spreadsheet.
They're pretty complex with four series in each graph.
How can I automate this task?
Question by:hbojorquezgeek
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2

Expert Comment

ID: 12377070
I'm not familiar with any of the methods, but i know its possible to do this with EXCEL 2003 and COM.  I know this won't win me any points, but I hope it gets you in the right direction.
LVL 11

Expert Comment

ID: 12377262
First step is to enable VBA and then do one time.

It means when you do it, automatically VBA code will be generated.

So just that VBA code.
Modifiy it accordingly that it becomes generic.

Then you can just put a loop where you pull the data from one point, insert into those automation code, then save those excel sheets.

Funda stuff.

Are you listen...? Reply me. So then we can discuss further

LVL 24

Accepted Solution

R_Rajesh earned 2000 total points
ID: 12377300
Hi SuperCableMan,
creating the chart shouldn't be a problem. setting the sourcedata range of each chart will be the tricky part. How is the data organised in your sheet? are they is some kind of order?
The following code assumes that your sheet1 has data in A1:D50. first chart's source will be A1:D5 the next chart A6:D10 and so on...

Sub AddChart()
Dim chtChart As Chart
For i = 1 To 50 Step 5
Set chtChart = Charts.Add
With chtChart
.Name = "chart" & i
.ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("Sheet1").Range("A" & i & ":D" & i + 4), PlotBy:=xlRows
.HasTitle = True
.ChartTitle.Text = "chart" & 1
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
Next i
End Sub

btw to use the code open your workbook hit alt+f11, this opens the vbe window. here select module form the insert menu and paste the code below. close the vbe window, back in xl, hit alt+f8 select AddChart and click on run. 50 charts will be created using the data A1:D50

LVL 24

Expert Comment

ID: 12377375
>>50 charts will be created using the data A1:D50
that should have been
10 charts will be created using the data A1:D50

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Starting up a Project

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question