Creating a chart from an external CSV file

[jachiavelli]

I don't know what I'm doing wrong here. I have a CSV file containing lots of entries. I made another CSV file and gave it the following macro:

Please Login or Register  to view this content.

This worked fine. The thing is, i want the file that's displaying the chart to be an XLS file, not a CSV. When I run the same macro in an XLS file, I get a run-time error 1004, Method 'Range'of object _Global failed.

I don't see what I'm doing incorrectly here. I've only changed the format of the file in which the macro is being run. I haven't changed any of the other files. I've tested and retested this. It really does just seem to break when it's run in an XLS file but works fine when it's run in a CSV. Any ideas?

All replies are appreciated. I've been working on this for hours.

[mrice]

I suspect that the reason is that you have the chart activated and this itself doesn't contain the range.

Maybe try ActiveChart.parent.parent.parent.range(...

[jachiavelli]

interesting idea. I'll try that out now

[jachiavelli]

That didn't work, unfortunately but I think I've nearly got it. I added in the variable "ws".

Please Login or Register  to view this content.

I don't know much about VBA but it's crashing here because I say "ActiveSheet" when the sheet isn't yet active, I suspect. I don't know enough about VBA to be sure. How can I say something like:

Please Login or Register  to view this content.

but without getting a type mismatch? There must be some way to specify the sheet name. I think that would make it work. I have a good feeling about that one.

Just so I'm sure, is this a sheet?:
http://img150.imageshack.us/my.php?image=sheet1qc1.jpg

I hope I'm not barking at the wrong bush.

Thanks for the reply

[mrice]

Try

Please Login or Register  to view this content.

[jachiavelli]

Thanks again

I tried that and got this error:
http://img521.imageshack.us/my.php?image=errorlc6.jpg

This should be so simple but I can't seem to get it. There must be something terribly obvious that I'm overlooking. I've uploaded the file, if you want to have a look:
http://www.geocities.com/mtwstorage/energyBooks.zip

Any ideas where I'm going wrong?

[mrice]

The following code gets you as a graph although it might not be quite the graph that you are expecting. Adjust the Open line to the appropriate path.

Please Login or Register  to view this content.

[jachiavelli]

Martin, that's absolutely perfect. Thank you so much. Sending lots of virtual kisses your way xxxxxxxxxxxxx :D