Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Create Excel Bar Charts in VBA

Posted on 2014-09-05
11
Medium Priority
?
184 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

926 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