Solved

Create Excel Bar Charts in VBA

Posted on 2014-09-05
11
175 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now