|
|
|
| Title | Use VBA code in Excel to import a CSV file and use it to make a line graph |
|---|
| Description | This example shows how to use VBA code in Excel to import a CSV file and use it to make a line graph. |
|---|
| Keywords | Excel, VBA, Visual Basic for Applications, import, CSV, comma separated value, import, line graph, chart |
|---|
| Categories | Office |
|---|
|
|
|
The following shows the format of the CSV (comma separated value) file containing the data.
|
|
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
|
| |
|
| |
|
|
