Use VBA code in Excel to import a CSV file and use it to make a line graph

[nandagopal]

The following shows the format of the CSV (comma separated value) file containing the data.

Code Select Expand


Date,Scores,Colors,Messes
10/1/08,10,5,3
10/2/08,15,8,12
10/3/08,7,9,7
10/4/08,11,14,9


When you click the worksheet's Import Data button, the following code calls subroutine ImportLineGraph.

After loading the data, the code makes a new Chart object using the imported data's result range as its source. The code sets a few chart values such as the axis titles and is done.


Private Sub cmdImportData_Click()
    ImportLineGraph "graphdata.csv", "Sheet1", "My Chart", _
        "Date", "Number"
End Sub


Subroutine ImportLineGraph first loads the CSV file. This example assumes that the file has the format shown earlier. In particular, it assumes that the file contains four columns containing a date and three values that should be formatted as general data.


Sub ImportLineGraph(ByVal file_name As String, ByVal _
    sheet_name As String, ByVal chart_title As String, _
    ByVal x_axis_title As String, ByVal y_axis_title As _
    String)
Dim work_sheet As Worksheet
Dim query_table As QueryTable
Dim new_chart As Chart

    ' Load the CSV file.
    Set work_sheet = Sheets(sheet_name)
    Set query_table = work_sheet.QueryTables.Add( _
        Connection:="TEXT;" & file_name, _
        Destination:=work_sheet.Range("A1"))
    With query_table
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False

        ' Set the data types for the columns.
        .TextFileColumnDataTypes = Array(xlMDYFormat, _
            xlGeneralFormat, xlGeneralFormat, _
            xlGeneralFormat)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    ' Make the line graph.
    query_table.Destination.Select
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData _
        Source:=query_table.ResultRange, _
        PlotBy:=xlColumns
    ActiveChart.Location Where:=xlLocationAsObject, _
        Name:=sheet_name
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = chart_title
        If Len(x_axis_title) = 0 Then
            .Axes(xlCategory, xlPrimary).HasTitle = False
        Else
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, _
                xlPrimary).AxisTitle.Characters.Text = _
                x_axis_title
        End If
        If Len(y_axis_title) = 0 Then
            .Axes(xlValue, xlPrimary).HasTitle = False
        Else
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, _
                xlPrimary).AxisTitle.Characters.Text = _
                y_axis_title
        End If
    End With
End Sub