Excel help... Automate Charts

[MyWaterloo]

Hi all. I am asking this question here because I have no idea where else to go with it. It involves VBA... but not Access. You guys have always been spot on with Access help so i thought maybe you could give me some direction or maybe tell me where to go with my question. I have an Excel question.

I have a list of data that is updated every day and exported from another program into an excel file. This data is in long columns of some 2500+ rows. The column headings are date, type, status, field name. Every block of 50 rows is various data for the same field name. I would like to automate chart making for each block of 50 rows. The chart will be exactly the same format with exactly the same amount of data. Here is what the macro creates for one chart:

Sub Macro12()
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = "='Sheet1'!$D$2:$D$50"
    ActiveChart.SeriesCollection(1).XValues = "='Sheet1'!$F$2:$F$50"
End Sub

How to have a chart created to represent every 50 rows of data? I suppose i could copy the code 50 times and find out what cells would be included in each section... but that seems so archaic. Ideas? Or where I should go post this question? Thanks all.

[ADezii]

Just subscribing for now, will see if any ideas pop up in my head...

[MyWaterloo]

Ya... It seems like it shouldn't be too difficult: Code for chart creation; Code to move 50+ rows and create a new chart using next 50 rows; and so on...but I can't get my brain around it... or find any similar posts through an internet search that could help.

[ADezii]

This Code has not been tested, so I really do not know how well, or if at all, it will work. You may have to tweak it a little, but hopefully it will point you in the right direction. Good Luck.

Dim intCtr As Integer
Const conNUM_OF_ROWS As Integer = 2500

For intCtr = 2 To conNUM_OF_ROWS Step 50
  ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
      .ChartType = xlLine
      .SeriesCollection.NewSeries
      .SeriesCollection(1).Values = "='Sheet1'!$D$" & CStr(intCtr) & ":$D$" & CStr(intCtr + 49)
      .SeriesCollection(1).XValues = "='Sheet1'!$F$" & CStr(intCtr) & ":$F$" & CStr(intCtr + 49)
    End With
Next

[MyWaterloo]

Wow! It worked perfect! I plugged in the code and boom! More charts than you can shake a stick at! If I could trouble you once more... How can I select the name for the charts? The Names reside in column B. I tried:

    Dim intCtr As Integer
    Const conNUM_OF_ROWS As Integer = 2646
   
    For intCtr = 2 To conNUM_OF_ROWS Step 49
      ActiveSheet.Shapes.AddChart.Select
        With ActiveChart
          [U][I]ActiveChart.Name = "='Sheet1'!$B$" & CStr(intCtr) & ":$B$" & CStr(intCtr + 48)[/I][/U]
          .ChartType = xlLine
          .SeriesCollection.NewSeries
          .SeriesCollection(1).Values = "='Sheet1'!$D$" & CStr(intCtr) & ":$D$" & CStr(intCtr + 48)
         .SeriesCollection(1).XValues = "='Sheet1'!$F$" & CStr(intCtr) & ":$F$" & CStr(intCtr + 48)
       End With
   Next

...but it doesn't work to try and name the charts like that. (I also discovered it is every 49 cells for chart data, hence the change in cells to count.) Also, all the charts are created as objects piled one on top of another, is there a way to tell them to spread out or align to a grid maybe? Like 5 across and 10 down? Thanks again, I will keep playing with this and keep checking back here also.

[MyWaterloo]

I figured out the naming issue:

        Dim intCtr As Integer
        Const conNUM_OF_ROWS As Integer = 2646
   
        For intCtr = 2 To conNUM_OF_ROWS Step 49
          ActiveSheet.Shapes.AddChart.Select
            With ActiveChart
              .ChartType = xlLine
              .SeriesCollection.NewSeries
              .SeriesCollection(1).Values = "='Sheet1'!$D$" & CStr(intCtr) & ":$D$" & CStr(intCtr + 48)
              .SeriesCollection(1).XValues = "='Sheet1'!$F$" & CStr(intCtr) & ":$F$" & CStr(intCtr + 48)
              [U][I].SeriesCollection(1).Name = "='Sheet1'!$B$" & CStr(intCtr + 48)[/I][/U]
        End With
      Next

This works great to creat the charts and give them the appropriate names. There is one strange anomaly, the charts are all created with a phantom 2nd series called "Series2". I can't figure out why unless there is a default series when a chart is created. Se attached image. "Series2" is also empty of data.

[MyWaterloo]

Ok, the extra series is from the:

.SeriesCollection.NewSeries

This is not needed as the chart is created with a default series that the rest of the code then defines with .Values and the .XValues.


I would like to add another cell address to the naming code so the name is derived from column B & E.

Current Code:

 .SeriesCollection(1).Name = "='Sheet1'!$B$" & CStr(intCtr + 48)

Change to something like:

.SeriesCollection(1).Name = "='Sheet1'!$B$" & "='Sheet1'!$E$" & CStr(intCtr + 48)

, but obviously this doesn't work.


So I am still attempting to find a way to align the charts to a grid, and trying to pull the name from two different cells.

[ADezii]

I would like to add another cell address to the naming code so the name is derived from column B & E.

Dim intCtr As Integer

intCtr = 2

MsgBox Worksheets("Sheet1").Range("B" & CStr(intCtr)) & _
       Worksheets("Sheet1").Range("C" & CStr(intCtr + 48))

[MyWaterloo]

Thanks! That works... I also found code for aligning the charts:

Sub AlignCharts()
  ' Jon Peltier (3/19/2008)
  ' http://peltiertech.com/WordPress/
  ' Changes MyWaterloo (3/13/2011)

  ' chart size - adjust as desired
  Const nRowsTall As Long = 13
  Const nColsWide As Long = 9

  ' chart layout - adjust as desired
  Const nChartsPerRow As Long = 3
  Const nSkipRows As Long = 2
  Const nSkipCols As Long = 1
  Const nFirstRow As Long = 3
  Const nFirstCol As Long = 1

  Dim iChart As Long
  Dim chtob As ChartObject
  Dim dWidth As Double
  Dim dHeight As Double
  Dim rData As Range
  Dim dFirstChartTop As Double
  Dim dFirstChartLeft As Double
  Dim dRowsBetweenChart As Double
  Dim dColsBetweenChart As Double

  With Worksheets("Sheet2").Cells(1, 1)
    dWidth = nColsWide * .Width
    dHeight = nRowsTall * .Height
    dFirstChartLeft = (nFirstCol - 1) * .Width
    dFirstChartTop = (nFirstRow - 1) * .Height
    dRowsBetweenChart = nSkipRows * .Height
    dColsBetweenChart = nSkipCols * .Width
  End With

  For iChart = 1 To Worksheets("Sheet2").ChartObjects.Count

    Set chtob = Worksheets("Sheet2").ChartObjects(iChart)

    With chtob
      .Left = ((iChart - 1) Mod nChartsPerRow) * (dWidth + dColsBetweenChart) + dFirstChartLeft
      .Top = Int((iChart - 1) / nChartsPerRow) * (dHeight + dRowsBetweenChart) + dFirstChartTop
      .Width = dWidth
      .Height = dHeight
    End With

  Next





End Sub

Thanks for all your help ADezii, I really appreciate it.

God Bless,
MyWaterloo

[ADezii]

@MyWaterloo - You really didn't need us at all! (LOL)