VBA Powerpoint 2010 create a chart from csv data

Question

I'm trying to create a makro to automatically generate charts in powerpoint 2010 from data stored in a csv file. I got as far as generating the chart and reading the csv file but now im stucked with displaying the data.

Here is my code so far:

Sub CreateChart()
Dim myChart As Chart
Dim gChartData As ChartData
Dim gWorkBook As Excel.Workbook
Dim gWorkSheet As Excel.Worksheet
Dim strPath As String
Dim lngLastRow As Long
Dim mySystemFileObject

' Create the chart and set a reference to the chart data.
Set myChart = ActivePresentation.Slides(1).Shapes.AddChart.Chart
Set gChartData = myChart.ChartData

' read the csv file
strPath = "C:\path\to\my\data.csv"

' Set the Workbook and Worksheet references.
Set gWorkBook = gChartData.Workbook
Set gWorkSheet = gWorkBook.Worksheets(1)


With gWorkSheet.QueryTables.Add(Connection:="TEXT;" & strPath,     Destination:=gWorkSheet.Range("A1"))
    .TextFileDecimalSeparator = "."
    .TextFileThousandsSeparator = " "
    .TextFileParseType = xlDelimited
    .TextFileCommaDelimiter = True
    .Refresh
End With
gWorkBook.RefreshAll

' Does not work, returns Runtime Error 1004 
'gWorkSheet.ListObjects("Tabelle1").Resize gWorkSheet.Range("A1:G5")

myChart.SetSourceData ("='Tabelle1'!A7:G74")
myChart.Refresh

'gWorkSheet.Range("A7:G74").Select

' Clean up the references.
Set gWorkSheet = Nothing
' gWorkBook.Application.Quit
gWorkBook.Close

Set gChartData = Nothing
Set myChart = Nothing

End Sub

Now the problem is to select the range A1:G74 with VBA and display the data in the chart. Probably I'm just missing the right function. Any ideas or help is appreciated!

Best regards, Jim

Answer 1

The Solution is, that one can only change one direction at a time.

So I solved the problem via reading the csv data as a text file, than resize the range of the chart and afterwards fill the chart with the data from the csv file

strRange = "A1:D" + CStr(UBound(tmpData.XValues))
            gWorkSheet.ListObjects("Tabelle1").Resize gWorkSheet.Range(strRange)
            ' change dim 2
            If ((UBound(myData, 2) * 2) + 1 > 4) Then
                strRange = "A1:" + HelperFunctions.ConvertToLetter2((UBound(myData, 2) * 2) + 1) + CStr(UBound(tmpData.XValues))
                gWorkSheet.ListObjects("Tabelle1").Resize gWorkSheet.Range(strRange)
            End If

Afterwards writing the data into the cells via:

For l = 1 To UBound(tmpData.XValues)

                    gWorkSheet.Cells(l, k + 1).Value = tmpData.XValues(l - 1)
                    gWorkSheet.Cells(l, k + 2).Value = tmpData.YValues(l - 1)
            Next