Creating a chart from an external CSV file

**mrice** · 03-19-2008, 04:20 PM

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** · 03-20-2008, 08:15 PM

interesting idea. I'll try that out now

**jachiavelli** · 03-20-2008, 08:50 PM

That didn't work, unfortunately

but I think I've nearly got it. I added in the variable "ws".

Private Sub Workbook_Open()

    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim n
    n = ActiveSheet.ChartObjects.Count
    For i = n To 1 Step -1
        ActiveSheet.ChartObjects(i).Delete
    Next i
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=ws.Range("[energyOutput.csv]energyOutput!$A")
    ActiveChart.ChartType = xlLine
    
End Sub

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:

Set ws = "worksheet1"

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** · 03-21-2008, 11:19 AM

Try

Set ws = ThisWorkbook.ActiveSheet

**jachiavelli** · 03-21-2008, 11:54 AM

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** · 03-21-2008, 02:00 PM

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.

Private Sub Workbook_Open()
Dim ws As Worksheet
    Set ws = ThisWorkbook.ActiveSheet
    Workbooks.Open "c:\energyOutput.csv"
    
    Dim n
    ThisWorkbook.Activate
    For i = ActiveSheet.ChartObjects.Count To 1 Step -1
        ActiveSheet.ChartObjects(i).Delete
    Next i
    
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=Workbooks("energyOutput.csv").Sheets("energyOutput").Range("$A1:$C" & Workbooks("energyOutput.csv").Sheets("energyOutput").Cells(65536, 1).End(xlUp).Row)
    ActiveChart.ChartType = xlLine
End Sub