Excel VBA Macro: create a chart from CSV?

Question

I have a CSV file with three columns (A,B,C).

I can record a Macro which selects Col A + Col B, then inserts a chart of A versus B.

This works, but the code generated contains a hardcoded ref to the 'Sheet1' like this:

...
ActiveChart.SetSourceData Source:=Range( _
        "'Sheet1'!$A:$A,'Sheet1'!$B:$B,'Sheet1'!$A:$A,'Sheet1'!$B:$B")
...

So I change this to match the active document like this:

...
ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A:$B")
...

This works, however I need to also insert a chart using COL A + COL C, the generated code looks like this:

...
ActiveChart.SetSourceData Source:=ActiveSheet.Range("'Sheet1'!$A:$A,'Sheet1'!$C:$C")
...

How to a similarily change this code to make it sheetname-agnostic ? [ The issue is that how to do I select two columns which are not adjacent to each other - I think I was luck in the first example - its a special case ]

Fionnuala · Accepted Answer · 2010-02-04 21:07:54Z

1

How about:

ActiveChart.SetSourceData Source:=ActiveSheet.Range("$A:$A,$C:$C")

answered Feb 4, 2010 at 21:07

Fionnuala

91.5k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

monojohnny Over a year ago

works great - I must have made a typing error - previously I was getting A+B+C in the graph for some reason. Thanks !

Collectives™ on Stack Overflow

Excel VBA Macro: create a chart from CSV?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related