1

I often make a lot of scatter plots (column j vs column i) in a single Worksheet. I want to export them as png/jpg files. Each plot would need a sensible file name. I have thought that the file name could be something like plot_[column i]_[column j].png.

How do I get the column (like C or AE) from each plot (or ActiveChart)? Then I can create a file name string to be fed in to the Export method. I am a complete beginner for VBA macros, but understand some Visual Basic.

user6439024
  • 79
  • 1
  • 1
  • 8
  • Record a macro while changing a column; change its color or something. If you study that macro, you'll find how to find them in the object model. – Sam Mar 26 '18 at 12:28

1 Answers1

0

You can extract that information from the source data string using text functions. The source data is available using .SeriesCollection:

activesheet.chartobjects("Chart 1").chart.SeriesCollection(1).Formula

will return something like this:

"=SERIES(,Sheet1!$A$1:$A$4,Sheet1!$B$1:$B$4,1)"

That contains the two columns you need, "A" and "B". You can extract them using text functions like INSTR(), MID(), and LEFT(). Here is an example using debug.print to output the columns. I'm assuming you already know how to export them since that was not included in your question.

Sub FindSourceColumns()
    Dim sourcedata, firstcolumn, secondcolumn As String, c as chartobject

    for each c in activesheet.chartobjects
        sourcedata = c.Chart.SeriesCollection(1).Formula
        firstcolumn = Mid(sourcedata, InStr(sourcedata, "!$") + 2, 5)
        firstcolumn = Left(firstcolumn, InStr(firstcolumn, "$") - 1)
        Debug.Print firstcolumn
        secondcolumn = Mid(sourcedata, InStr(InStr(sourcedata, "!$") + 2, sourcedata, "!$") + 2, 5)
        secondcolumn = Left(secondcolumn, InStr(secondcolumn, "$") - 1)
        Debug.Print secondcolumn
    next c
End Sub
Greg Viers
  • 3,473
  • 3
  • 18
  • 36
  • I think this should work fine. Is it possible to go through all plots and do the same in a For loop? And can macro code make different plots as ActiveChart in a loop and then I can write sourcedata = ActiveChart.SeriesCollection(1).Formula ? Thanks for your quick help. – user6439024 Mar 26 '18 at 12:36
  • Rather than use activechart, I would declare a variable, loop using: for each c in activesheet.chartobjects. I'll update my answer. – Greg Viers Mar 26 '18 at 12:48
  • When the plots have more complicated selections than just two small blocks in two columns, this does not work well. Like A5, A9:A12 vs D5, D9:D12 produces A as the firstcolumn as well as secondcolumn. Can you think of a nice way to handle this? – user6439024 Apr 21 '18 at 14:30
  • You could reverse the string and search backwards in it. – Greg Viers Apr 25 '18 at 12:27