3

I often find myself copying and pasting worksheets in order to show the same analysis for different sets of data. This includes graphs. It's irritating having to 'select data' for every data series, error bars, etc. for every data set.

Once I have created a graph for one worksheet, my choices are:

  • Copy the graph to another worksheet. Use 'select data' for every part
  • Copy the entire worksheet, then replace the data

I would much prefer to be able to see the XML (or whatever) representation behind the graph and be able to do a find-and-replace on the worksheet names/column names. The Excel interface gets in the way a lot and doesn't make it easy to change the graph repeatedly (particularly for error bars).

Is there some textual representation of objects like graphs I can edit? (I'd rather not mess around with extracting the xlsx file and editing the XML files in a text editor).

  • You should be able to modify your charts using VBA. Also, if applicable, modify the chart source tables instead of the charts. – mtone Feb 3 '13 at 18:44
0

Insert data to Excel 2007 from ...

link dato to excel

Open CSV file

Open CSV file

open wizard part I

open wizard part I

open wizard part II

open wizard part II

open wizard part III

open wizard part III

insert cell choice

insert cell choice

refreash data options from CSV file

refreash data options from  CSV file

view CSV file in Far Manager

view CSV file in Far Manager

Set type of chart

set type of chart

Edit CSV file

Edit CSV file

Refreash Excel data

Refreash Excel data

Edit CSV file

Edit CSV file

Refreash Excel data

Refreash Excel data

Security data setting

Security data setting

It all. If need insert rotate table and graph picture).

  • At this point it's probably simpler to feed your data from a database (such as sql server) rather than edit CSV files, for the same result. – mtone Feb 3 '13 at 18:40
  • Yes, easier to use ms sql server. You can add screens to connect and work with a database and write stored procedures. I do not mind. – STTR Feb 3 '13 at 18:50
0

How about editing the series formulas? I have a tutorial at Change Series Formula – Improved Routines, but the gist is as follows:

The series formula looks like this: =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$11,Sheet1!$B$2:$B$11,1)

The arguments mean: =SERIES([Series Name],[X Values],[Y Values],[Plot Order])

You access these formulas in VBA using something like ActiveChart.SeriesCollection(1).Formula

You can use the following to change a part of the formula from `OldString' to 'NewString':

ActiveChart.SeriesCollection(1).Formula = WorksheetFunction.Substitute( _
    ActiveChart.SeriesCollection(1).Formula, OldString, NewString)

You can wrap this into a nice VBA procedure that asks the user for that old string to replace with what new string, then makes the changes to all series in the active chart:

Sub ChangeSeriesFormula()
    ''' Just do active chart
    If ActiveChart Is Nothing Then
        '' There is no active chart
        MsgBox "Please select a chart and try again.", vbExclamation, _
            "No Chart Selected"
        Exit Sub
    End If

    Dim OldString As String, NewString As String, strTemp As String
    Dim mySrs As Series

    OldString = InputBox("Enter the string to be replaced:", "Enter old string")

    If Len(OldString) > 1 Then
        NewString = InputBox("Enter the string to replace " & """" _
            & OldString & """:", "Enter new string")
        '' Loop through all series
        For Each mySrs In ActiveChart.SeriesCollection
            strTemp = WorksheetFunction.Substitute(mySrs.Formula, _
                OldString, NewString)
            mySrs.Formula = strTemp
        Next
    Else
        MsgBox "Nothing to be replaced.", vbInformation, "Nothing Entered"
    End If
End Sub

The tutorial I cited above has more examples of how to edit series formulas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.