Solved

Create Excel Bar Charts in VBA

Posted on 2014-09-05
11
179 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

740 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