Solved

Create Excel Bar Charts in VBA

Posted on 2014-09-05
11
181 Views
Last Modified: 2014-09-14
I have to create around a hundred bar charts in excel based on the data returned from an SQL query.

Sample Data:
WorkCentre      DATE                           Available      Planned              MRPSuggested
C201              17/09/2014 00:00                3031      250      
C201              18/09/2014 00:00             031      966      
C201              15/09/2014 00:00                3031      370      
C201              12/09/2014 00:00                3031      368                      186
C201              10/09/2014 00:00                3031      159                      310
C201              16/09/2014 00:00                5031      370                      892
C202              04/09/2014 00:00                  936      64.5                      516
C202              18/09/2014 00:00                  936      1677      
C202              05/09/2014 00:00            936      64.5                      709.5
C202             16/09/2014 00:00                 936      838.5      
C202             11/09/2014 00:00                 936      645                      774
C202             17/09/2014 00:00                436      516      

I can create the Pivot tables manually for each WorkCenter see attached file.
I have then created a chart sheet showing four of the WorkCenters on each sheet.

Is it possible to create the charts in VBA and then show them four to a sheet?
the manual solution I am working on works ok until a new WorkCenter is added then I have to shuffle the charts and and a new sheet.

This may not be possible to complete using VBA
0
Comment
Question by:RickCooper
[X]
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
  • 6
  • 5
11 Comments
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40305476
No attachment
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40305499
Sorry File attached
Chart.xlsx
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40305514
Can you please help with the data. it contains only resultant pivot table and chart
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 1

Author Comment

by:RickCooper
ID: 40305522
Sample data attached.
It is from part of the results from the SQL query
SampData.xlsx
0
 
LVL 11

Accepted Solution

by:
Swapnil Nirmal earned 500 total points
ID: 40305575
Use the macro1 in attached file.
pivot-Chart.xlsm
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40305640
I have managed to create the graph using VBA but I have to select the Range myself.
Can the code be changed to change the range when the WorkCentre changes and create a new graph

Sub EmbeddedChart()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long


    ' define chart data
    Set rngChtData = Sheets("Sheet1").Range("$B$1:$E$26")

    ' define chart's X values
    With rngChtData
        Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
    End With

    ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=400, Width:=375, Top:=75, Height:=225)
    With myChtObj.Chart

        ' make an XY chart
        .ChartType = xlColumnClustered

        ' remove extra series
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

        ' add series from selected range, column by column
        For iColumn = 2 To rngChtData.Columns.Count
            With .SeriesCollection.NewSeries
                .Values = rngChtXVal.Offset(, iColumn - 1)
                .XValues = rngChtXVal
                .Name = rngChtData(1, iColumn)
            End With
        Next

    End With

End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40307250
I am little bit confused. I hope you are creating a pivot chart. in that case u need not to select any range. It will automatically select the pivot table.

When you have to select a range manually?
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40307660
Hi I am trying to create a chart which shows availability, panned and wip. For each day with a chart for each work centre.

I used a pivot table and filtered on the work centre. It means I have to create a new one for each workcentre as I copy the chart to a sheet which shows all the charts in one place.
0
 
LVL 1

Author Comment

by:RickCooper
ID: 40311822
HI,

 Sorry for the late reply I have not been in the office,
I have just checked your pivot-Chart workbook and it works as I asked with a couple of things which need changing.
I need the Chart Title to show the Work Center and none of the filter buttons to be shown on the chart.
I also needed the charts on one sheet.

I will see if I can change the macro to achieve the above.

Thanks again
0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 40321475
Have added chart title in the each of the pivots. However making all those pivots in one sheet is not possible
pivot-Chart.xlsm
0
 
LVL 1

Author Closing Comment

by:RickCooper
ID: 40321792
Thanks for your help. I modified your solution and added to the macro.

It moves each chart to a sheet called charts. It the arranges them on the sheet and hides the sheets which are used to create each chart.

I will post the code when I am next in the office.

Thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 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