Create Excel Bar Charts in VBA

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
LVL 1
RickCooperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swapnil NirmalManager, Audit AnalyticsCommented:
No attachment
0
RickCooperAuthor Commented:
Sorry File attached
Chart.xlsx
0
Swapnil NirmalManager, Audit AnalyticsCommented:
Can you please help with the data. it contains only resultant pivot table and chart
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

RickCooperAuthor Commented:
Sample data attached.
It is from part of the results from the SQL query
SampData.xlsx
0
Swapnil NirmalManager, Audit AnalyticsCommented:
Use the macro1 in attached file.
pivot-Chart.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RickCooperAuthor Commented:
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
Swapnil NirmalManager, Audit AnalyticsCommented:
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
RickCooperAuthor Commented:
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
RickCooperAuthor Commented:
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
Swapnil NirmalManager, Audit AnalyticsCommented:
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
RickCooperAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.