In a recent Mr Excel post, a member asked how to convert a CSV File to Excel File with Dynamic Graphing Capability. This is a great topic for a tutorial, but too long for a forum answer.
Problem Statement
The simple statement was this:
- Convert a CSV file to an Excel workbook
- Create a chart based on
- User-specified chart type
- User-specified columns for X and Y
I expanded on this a little bit:
- Open a user-selected CSV file
- Save as an Excel workbook
- Display dialog for user to select
- Chart type
- Columns containing X and Y values for chart
- Create the desired chart
This is a pretty easy project, which would generally take me half a day or less, depending on other distractions.
Approach
Essentially, I started with this simple VBA procedure, and a similarly simple UserForm (dialog).
Sub OpenCSVFileAndPlotData()
' 1. Get CSV file name
' 2. Open CSV file
' 3. Save as workbook
' 4. Parse file
' 5. Show dialog (select chart type, X values, Y values)
' 6. Draw chart
' 7. Save file
End SubMy first time through development, I did the least I had to do to make it work. The second time through, I added a bunch of nice embellishments that should make things easier for users to just pick it up and run with it.
For both levels, the following sections have pieces of code describing what is happening, the complete code, and an example workbook.
Building a Simple Solution
Code Workbook
I’m using a macro-enabled Excel workbook named My_CSV_Data_Processor.xlsm. It has a single worksheet named Parameters.
Saved Information: Named Range Containing Chart Types
The worksheet contains a list of chart types that our program will create. These are the simplest and most useful charts built into Excel. The list has two columns, the first being the integer values of Excel’s chart type constants (e.g., xlArea = 1, xlXYScatter = -4169), the second being the chart type names that will appear in the dialog.
The range A2:B12 has been named ChartTypes, so it will be easy for the code to put these values into the dialog.
Starting the Program: Form Control Button
To add a button, the Developer tab must be visible on the ribbon. If you don’t see it, right-click on a blank place in the ribbon, choose Customize the Ribbon. In the list on the right (Main Tabs), click the box in front of Developer.
On the Developer tab, in the Controls group, click the Insert dropdown, then under Form Controls, click on the first icon, Button. Draw a button on the worksheet.
A dialog will pop up asking which VBA procedure to assign to the button; you can revisit this dialog by right-clicking on the button. Select OpenCSVFileAndPlotData and press OK. Then type a caption on the button and resize it as needed.
Code Module
We need to put the code into a regular code module. In the VB Editor, find the workbook’s project in the Project Explorer pane, right click anywhere on it, and choose Insert > Module. When a new code module opens up in the editor, rename it to something useful like MChartFromCSVFile using the Properties pane. The exact name you use isn’t critical, but it should be somewhat descriptive.
On the View menu, click Project Explorer (Ctrl+R) or Properties (F4) if these panes are not visible.
Getting Ready
The first line of the module should be
Option ExplicitIf this statement doesn’t appear, type it in, then go to Tools menu > Options, and check the Require Variable Declaration box. While you’re in the Options dialog, uncheck Auto Syntax Check.
After a blank line or two, type Sub and the name of the procedure. The VB Editor automatically skips another line and types End Sub for you:
Sub OpenCSVFileAndPlotData()
End SubThe main program will be typed between Sub and End Sub. All the variables will be declared at the top using Dim statements. Variables don’t need to be declared at the top of a routine, but VBA offers no just-in-time benefit to declaring them right where you start using them, and I like having them in one place so I can find their declarations easily. In the sections below I will insert a simple Dim statement for the variables used.
1. Get CSV File Name
We can use Excel’s GetOpenFileName to allow the user to select a CSV file from his computer.
Dim sCSVFullName As String
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
"Select a CSV file", , False)We’ve specified the CSV file filter, indicated a title for the Open File dialog, and said False to selecting multiple files.
2. Open CSV file
This part is easy, we just need to open the file named in the previous step. Then we’ll set a workbook object variable to this file, so it’s easy to reference later in the code.
Dim wb As Workbook
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook3. Save as workbook
We need to remove the file extension from the CSV file name (which includes the file path), which we do by retaining only text up to the last period in the file name. Then we add the Excel workbook file extension, and save the file, specifying the default workbook format.
Dim sWbkFullName As String, sFileRoot As String
sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
wb.SaveAs sWbkFullName, xlWorkbookDefault4. Parse file
Now the file is open, so we’ll extract some information to place in the dialog. We’ll use a two-column display, where the first column has the column number, and the second a simple comma-delimited list of the first few values in that column.
Dim ws As Worksheet, rng As Range, vRng As Variant
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
Dim sTemp As String
Dim vChartData As Variant
Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count
'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol ' column number
sTemp = ""
For iRow = 1 To 4 ' first 4 values
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left$(sTemp, Len(sTemp) - 2) ' remove last comma
vChartData(iCol, 2) = sTemp
Next5. Show dialog
We need to instantiate the UserForm (i.e., load it into memory), pass in the array of column numbers and initial column values, and display the form. At this point, code stops and waits for the user to make his selections and dismiss the form.
When the form is dismissed, we need to get the user’s selections: chart type, whether the CSV file has header labels in the first row, and the columns to be used for X and Y values in the chart. Then we remove the UserForm from memory.
Dim bFirstRowHeaders As Boolean
Dim myChartType As XlChartType
Dim vX As Variant, vY As Variant
Dim frmChartFromCSVFile As FChartFromCSVFile
Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData
.Show
'' get information selected by user
myChartType = .ChartType
bFirstRowHeaders = .FirstRowHeaders
vX = .Xcolumns
vY = .YColumns
End With
Unload frmChartFromCSVFile6. Draw chart
We need to figure out how to separate the data into separate series, then we need to get the data into the chart.
In XY charts, we let the user create one or more series, where (a) all series share the same set of X values (or no X values if the user didn’t specify them, and Excel will use 1, 2, 3, etc. as X values) and each series has a unique set of Y values, (b) all series share the same set of Y values and each has a unique set of X values, or (c) each series has its own unique sets of X and Y values.
For other chart types, the only relevant combination of X and Y is (a), since Excel uses the same X values for all series regardless of how we specify them. We will deal with this in the Advanced version of this program.
Excel 2013 introduced AddChart2 as an improvement over Excel 2007’s AddChart method. AddChart is hidden in Excel 2013, but AddChart2 will crash Excel 2007 and 2010, so we will use AddChart. In the Advanced program, we will enhance the code to use Excel 2013’s improved default styles while still using the error-free AddChart method.
After adding the chart, we add one series at a time, adding its Y values, X values, and name separately.
Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim rCht As Range, cht As Chart, srs As Series
'' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
If bFirstRowHeaders Then
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If
'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
'' chart type
With cht
If myChartType <> 0 Then
.ChartType = myChartType
End If
'' add series
For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If bFirstRowHeaders Then
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
Address(True, True, xlA1, True)
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
Address(True, True, xlA1, True)
End If
End If
End With
Next
End With7. Save file
Simple: save changes.
wb.SaveUserForm
Right click on the workbook’s project in the Project Explorer pane, click Insert > UserForm. When the UserForm appears, give it the name FChartFromCSVFile in the properties pane. This name is descriptive, and is how your code references the UserForm.
UserForm Controls
The Userform contains the following important controls, with the important properties shown below:
btnOK – OK button
Default: True
lstChartType – Listbox with two columns, one hidden.
ColumnCount: 2
ColumnWidths: 0 pt;112 pt
Width: 120
Height: 150
lstChartData – Listbox with two columns, extended multiselect
ColumnCount: 2
ColumnWidths: 42 pt;145 pt
Width: 195
Height: 150
MultiSelect: 2 - fmMultiSelectExtended
lstX, lstY – simple listboxes
lblbtnX, lblbtnY, lblbtnReset – “Label Buttons” or Labels with raised appearance (like regular buttons)
SpecialEffect: 1 - fmSpecialEffectRaised
chkFirstRowHeaders – Checkbox
Plus a few labels that help the user understand the dialog.
UserForm Code
Right click on the UserForm in the Project Explorer, and click on View Code. The Dialog’s code module will appear. Much of this code responds to events on the UserForm, events like clicking buttons. This code also includes properties, which allow the calling procedure to pass information into the UserForm and get information back from it.
As with the regular code module, the UserForm module should begin with
Option ExplicitIf a control on the UserForm is to have code associated with it, double-click on the control and the VB Editor will insert a short stub of code into the module. For example, if you double-click on the OK button (named btnOK), the Editor will insert this:
Private Sub btnOK_Click()
End SubTo make the code useful, we only need to insert our statements within this stub.
When the user clicks the OK button, we want the form to be hidden but remain in memory:
Private Sub btnOK_Click()
Me.Hide
End SubWhen the UserForm is first loaded, we want the information from the named range ChartTypes to appear in the listbox lstChartTypes. The UserForm_Initialize code runs when the UserForm is loaded, and the code shown here does the magic:
Private Sub UserForm_Initialize()
Dim vChartTypes As Variant
vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
Me.lstChartTypes.List = vChartTypes
End SubThe button-formatted labels need some simple code attached, so that clicking them will populate the X and Y column lists. Click the lblbtnX label button to populate the lstX listbox:
Private Sub lblbtnX_Click()
Dim iLst As Long
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Me.lstX.AddItem iLst
End If
Next
End SubClick the lblbtnY label button to populate the lstY listbox:
Private Sub lblbtnY_Click()
Dim iLst As Long
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Me.lstY.AddItem iLst
End If
Next
End SubClick the lblbtnReset lable button to clear the X and Y listboxes and start over:
Private Sub lblbtnReset_Click()
Me.lstX.Clear
Me.lstY.Clear
End SubWe need the ChartData property to pass the information to display in the lstChartData listbox of the UserForm:
Public Property Let ChartData(vData As Variant)
Me.lstChartData.List = vData
End PropertyWe also needproperties to let us extract information from the UserForm: whether the first row of the CSV file has header labels, the selected chart type, and the X and Y columns to be plotted:
Property Get FirstRowHeaders() As Boolean
FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property
Public Property Get ChartType() As XlChartType
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartType = CLng(.List(.ListIndex, 0))
End If
End With
End Property
Public Property Get Xcolumns() As Variant
Xcolumns = Me.lstX.List
End Property
Public Property Get YColumns() As Variant
YColumns = Me.lstY.List
End PropertyThe Simple Tool
Code Module MChartFromCSVFile
Here is the complete listing of MChartFromCSVFile:
Option Explicit
Sub OpenCSVFileAndPlotData()
Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
Dim wb As Workbook, ws As Worksheet, rng As Range, vRng As Variant
Dim rCht As Range, cht As Chart, srs As Series
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long
Dim sTemp As String
Dim vChartData As Variant
Dim bFirstRowHeaders As Boolean
Dim myChartType As XlChartType
Dim vX As Variant, vY As Variant
Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim frmChartFromCSVFile As FChartFromCSVFile
' 1. Get CSV file name
sCSVFullName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , _
"Select a CSV file", , False)
' 2. Open CSV file
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook
' 3. Save as workbook
sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, ".") - 1)
sWbkFullName = sFileRoot & ".xlsx"
wb.SaveAs sWbkFullName, xlWorkbookDefault
' 4. Parse file
Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count
'' info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol
sTemp = ""
For iRow = 1 To 4
If iRow > iRows Then Exit For
sTemp = sTemp & vRng(iRow, iCol) & ", "
Next
sTemp = Left$(sTemp, Len(sTemp) - 2)
vChartData(iCol, 2) = sTemp
Next
' 5. Show dialog (get chart type, X values, Y values)
Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
'' pass in information we know
.ChartData = vChartData
.Show
myChartType = .ChartType
bFirstRowHeaders = .FirstRowHeaders
vX = .Xcolumns
vY = .YColumns
End With
Unload frmChartFromCSVFile
' 6. Draw chart
'' define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 - LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 - LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
If bFirstRowHeaders Then
Set rCht = rng.Offset(1).Resize(iRows - 1)
Else
Set rCht = rng
End If
'' select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
Set cht = ws.Shapes.AddChart.Chart '' Excel 2007+
''Set cht = ws.Shapes.AddChart2.Chart '' Excel 2013 only
'' chart type
With cht
.ChartType = myChartType
'' add series
For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
' X values
If nX = 0 Then
' no X values specified
ElseIf nX = 1 Then
' all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
' each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs - 1, 0)))
End If
' Y values
If nY = 1 Then
' all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
' each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs - 1, 0)))
End If
' series name
If bFirstRowHeaders Then
If nSrs = nY Then
.Name = "=" & rng.Cells(1, CLng(vY(iSrs - 1, 0))). _
Address(True, True, xlA1, True)
ElseIf nSrs = nX Then
.Name = "=" & rng.Cells(1, CLng(vX(iSrs - 1, 0))). _
Address(True, True, xlA1, True)
End If
End If
End With
Next
End With
' 7. Save file
wb.Save
ExitProcedure:
End SubUserForm Module FChartFromCSVFile
Here is the complete listing of FChartFromCSVFile:
Option Explicit
Private Sub btnOK_Click()
Me.Hide
End Sub
Private Sub lblbtnReset_Click()
Me.lstX.Clear
Me.lstY.Clear
End Sub
Private Sub lblbtnX_Click()
Dim iLst As Long
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Me.lstX.AddItem iLst
End If
Next
End Sub
Private Sub lblbtnY_Click()
Dim iLst As Long
For iLst = 1 To Me.lstChartData.ListCount
If Me.lstChartData.Selected(iLst - 1) Then
Me.lstY.AddItem iLst
End If
Next
End Sub
Public Property Let ChartData(vData As Variant)
Me.lstChartData.List = vData
End Property
Property Get FirstRowHeaders() As Boolean
FirstRowHeaders = Me.chkFirstRowHeaders.Value
End Property
Public Property Get ChartType() As XlChartType
With Me.lstChartTypes
If .ListIndex > -1 Then
ChartType = CLng(.List(.ListIndex, 0))
End If
End With
End Property
Public Property Get Xcolumns() As Variant
Xcolumns = Me.lstX.List
End Property
Public Property Get YColumns() As Variant
YColumns = Me.lstY.List
End Property
Private Sub UserForm_Initialize()
Dim vChartTypes As Variant
vChartTypes = ThisWorkbook.Names("ChartTypes").RefersToRange.Value2
Me.lstChartTypes.List = vChartTypes
End SubWorkbook with Working Code
You can download the simple workbook My_CSV_Data_Processor.xlsm to see all of this code in one place, and to see how it works.
Advanced Version
The next blog post has a number of enhancements that make things easier for the user and prevent various warning messages: VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User.
Daniel Hook says
Hi Jon. Thank you for sharing your blog “VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User”. I am not a developer but would like to write a macro that uses much of your code. Basically the company I work for cook food in a large computer controlled oven and the temperatures, time, etc. are saved as a CSV file. We need to prove that the food has reached the proper temperature so the chef will need to open the CSV file and print a chart. I would rather that he did not have to choose which column is on x / y axis etc. Apart from choosing the CSV file I don’t want them to have to choose anything so I have tried making some changes but always get errors. If there is anything you can share I would be extremely grateful, as trying to reverse engineer your code is proving tricky for me.
Kind regards,
Dan
Jon Peltier says
Daniel –
If all you need is a quick and dirty chart, your code can be much simpler. The following lets the user select a csv file, then opens it and makes a line chart using Excel’s default interpretation of the data. If there is only one column of data, those are the Y values, and Excel uses 1, 2, 3, etc. as the X values. If there are two or more columns and the first is formatted as times or dates or if it is text, teh first column is used for X values and the rest as Y values.
The code:
You can download a workbook with this code and with a button on the worksheet that runs the code.
Daniel Hook says
Hi Jon,
Thank you so much for writing the ‘quick and dirty’ code for me. One day soon I hope this sort of project may be possible for me, but right now this has been a God send. I am now in a position to add a few tweaks to make it exactly what we need. If you were in the UK I’d ask the company to send you some of our food but a sincere thank you must suffice.
I will continue watching your blog and wish you all the very best,
Daniel
Larry Kiser says
I have been researching for a way to do a few basic things and it seems you are knowledgeable and respond to questions.
I have a VBA file that opens a csv and graphs the data from the CSV format. But my challenges are that it tries to plot the first row which is header data, so I need to skip or erase it. And secondly, my data comes in with a date column and time column, but I need the plot to use both time and date to graph it in case there are same timestamps on different days. Below is my code, I have left in a delete row statement but doesnt work when I open the file, only if I hit f5 while in the editor.
Private Sub Workbook_Open()
‘Written by Larry Kiser with ESI 12/2016
‘This section is just declaring variables
Dim fso As New FileSystemObject
Dim fnum
Dim MyFile As String
Dim strDataLine As String
Dim x0Var() As Variant ‘First column, index
Dim x1Var() As Variant ‘Date
Dim x2Var() As Variant ‘Time
Dim y1Var() As Variant ‘Col D, first data range
Dim y2Var() As Variant
Dim y3Var() As Variant
‘Dim y4Var() As Variant
‘Dim y5Var() As Variant
‘Dim y6Var() As Variant
‘Dim y7Var() As Variant
‘Dim y8Var() As Variant
‘Dim y9Var() As Variant
‘Dim y10Var() As Variant
‘Dim y11Var() As Variant
‘Dim y12Var() As Variant
‘Dim y13Var() As Variant
‘Dim y14Var() As Variant
‘Dim y15Var() As Variant
‘Dim y16Var() As Variant
‘Dim y17Var() As Variant
‘Dim y18Var() As Variant
‘Dim y19Var() As Variant
‘Dim y20Var() As Variant
‘Dim y21Var() As Variant
‘Dim y22Var() As Variant
‘Dim y23Var() As Variant
‘Dim y24Var() As Variant
‘Dim y25Var() As Variant
‘Dim y26Var() As Variant
‘Dim y27Var() As Variant
‘Dim y28Var() As Variant
‘Dim y29Var() As Variant
‘Dim y30Var() As Variant
Dim intNumOfLines As Long
‘This is defining where the CSV file is stored
‘which should be the default USB drive location when inserted to this PC
‘You will need to put this path to wherever your data file is located
MyFile = “G:\16228\Trend Data\MYDATA.csv”
fnum = FreeFile()
Open MyFile For Input As #1
‘This was my attempt to delet the first row and it works when I hit f5 in this routine but not when it opens
Rows(1).EntireRow.Delete
intNumOfLines = 0
Do While Not EOF(1)
intNumOfLines = intNumOfLines + 1
Input #1, strDataLine
Input #1, strDataLine
Loop
Close #1
ReDim x0Var(intNumOfLines)
ReDim x1Var(intNumOfLines)
ReDim x2Var(intNumOfLines)
ReDim y1Var(intNumOfLines)
ReDim y2Var(intNumOfLines)
ReDim y3Var(intNumOfLines)
‘ReDim y4Var(intNumOfLines)
‘ReDim y5Var(intNumOfLines)
‘ReDim y6Var(intNumOfLines)
‘ReDim y7Var(intNumOfLines)
‘ReDim y8Var(intNumOfLines)
‘ReDim y9Var(intNumOfLines)
‘ReDim y10Var(intNumOfLines)
‘ReDim y11Var(intNumOfLines)
‘ReDim y12Var(intNumOfLines)
‘ReDim y13Var(intNumOfLines)
‘ReDim y14Var(intNumOfLines)
‘ReDim y15Var(intNumOfLines)
‘ReDim y16Var(intNumOfLines)
‘ReDim y17Var(intNumOfLines)
‘ReDim y18Var(intNumOfLines)
‘ReDim y19Var(intNumOfLines)
‘ReDim y20Var(intNumOfLines)
‘ReDim y21Var(intNumOfLines)
‘ReDim y22Var(intNumOfLines)
‘ReDim y23Var(intNumOfLines)
‘ReDim y24Var(intNumOfLines)
‘ReDim y25Var(intNumOfLines)
‘ReDim y26Var(intNumOfLines)
‘ReDim y27Var(intNumOfLines)
‘ReDim y28Var(intNumOfLines)
‘ReDim y29Var(intNumOfLines)
‘ReDim y30Var(intNumOfLines)
Open MyFile For Input As #1
intNumOfLines = 0
Do While Not EOF(1)
Input #1, x0Var(intNumOfLines)
Input #1, x1Var(intNumOfLines)
Input #1, x2Var(intNumOfLines)
Input #1, y1Var(intNumOfLines)
Input #1, y2Var(intNumOfLines)
Input #1, y3Var(intNumOfLines)
‘Input #1, y4Var(intNumOfLines)
‘Input #1, y5Var(intNumOfLines)
‘Input #1, y5Var(intNumOfLines)
‘Input #1, y6Var(intNumOfLines)
‘Input #1, y7Var(intNumOfLines)
‘Input #1, y8Var(intNumOfLines)
‘Input #1, y9Var(intNumOfLines)
‘Input #1, y10Var(intNumOfLines)
‘Input #1, y11Var(intNumOfLines)
‘Input #1, y12Var(intNumOfLines)
‘Input #1, y13Var(intNumOfLines)
‘Input #1, y14Var(intNumOfLines)
‘Input #1, y15Var(intNumOfLines)
‘Input #1, y16Var(intNumOfLines)
‘Input #1, y17Var(intNumOfLines)
‘Input #1, y18Var(intNumOfLines)
‘Input #1, y19Var(intNumOfLines)
‘Input #1, y20Var(intNumOfLines)
‘Input #1, y21Var(intNumOfLines)
‘Input #1, y22Var(intNumOfLines)
‘Input #1, y23Var(intNumOfLines)
‘Input #1, y24Var(intNumOfLines)
‘Input #1, y25Var(intNumOfLines)
‘Input #1, y26Var(intNumOfLines)
‘Input #1, y27Var(intNumOfLines)
‘Input #1, y28Var(intNumOfLines)
‘Input #1, y29Var(intNumOfLines)
‘Input #1, y30Var(intNumOfLines)
intNumOfLines = intNumOfLines + 1
Loop
Close #1
With Sheet1.VBAChart
.Clear
.Refresh
Set oChart = .Charts.Add
oChart.HasTitle = True
oChart.Title.Caption = “Oven 4 Bake Times”
‘oChart.Interior.Color = “blue”
oChart.PlotArea.Interior.Color = “white”
Set o1Series = oChart.SeriesCollection.Add
With o1Series
.Caption = “AGING TC”
.SetData chDimCategories, chDataLiteral, x2Var
.SetData chDimValues, chDataLiteral, y1Var
.Line.Color = “green”
‘.Line.DashStyle = chLineDash
.Line.Weight = 2
.Type = chChartTypeLine
End With
Set o2Series = oChart.SeriesCollection.Add
With o2Series
.Caption = “SOAK TC”
.SetData chDimCategories, chDataLiteral, x2Var
.SetData chDimValues, chDataLiteral, y2Var
.Line.Color = “blue”
‘.Line.DashStyle = chLineDash
.Line.Weight = 2
.Type = chChartTypeLine
End With
Set o3Series = oChart.SeriesCollection.Add
With o3Series
.Caption = “HTL TC”
.SetData chDimCategories, chDataLiteral, x2Var
.SetData chDimValues, chDataLiteral, y3Var
.Line.Color = “red”
‘.Line.DashStyle = chLineDash
.Line.Weight = 2
.Type = chChartTypeLine
End With
oChart.HasLegend = True
oChart.Legend.Position = chLegendPositionBottom
End With
End Sub
Jon Peltier says
Larry –
You’ve made it way more complicated than it needs to be, and you don’t seem to have tried to implement the example I’ve provided. There is syntax here which is not Excel VBA syntax and doesn’t resemble any syntax in my example.
Here is what you need in your own program:
1. Open CSV file, save as Excel workbook (doesn’t use FileSystemObject, which you declared but didn’t use, doesn’t use old VB “Open MyFile For Input” syntax, simply opens the CSV file):
2. Insert a new column
3. Insert a simple formula to sum Date and Time
4. Define the chart data (the date-time column plus the columns of Y values you want to plot), insert a chart, set its data to the chart data range
Larry Kiser says
Thank you for the reply and help Jon. I thought it would be easier to continue down the path I was already on because I this is not my area of expertise. Two weeks ago I did not know any of this could be done and I felt very close. I started walking down this path now but it seems the message was abruptly cut off? I can follow through some of it and I get so far as to creating the new xlsx file but I get an error (presumably because it isnt finished). I do not see a new column added though.
My data has 6 columns, should I use 6 instead of your 4?
And I previously used/referenced an embedded chart:
=EMBED(“OWC11.ChartSpace.11″,””)
Will I still be using that same chart to actually display it? Are the references as simple as say nRows(x) is the x row and so forth?
Ill keep reading through this guide as it pertains to my issue and see if I can piece the rest of it together while waiting. Any further information would be very helpful.
Jon Peltier says
Larry –
This tutorial is probably the intermediate one for opening a CSV file and plotting the data. There is a more advanced one than this, but I don’t have the simpler one. Maybe I should write that now, so you also can benefit.
What was the error when you tried creating the xlsx file, and on what line of code did it appear? Include the error message, since Microsoft recycles error numbers. A good way to fix errors while programming in Excel VBA is to type “Excel VBA” (without quotes), a keyword relating to your task, and the error message into a Google search. You’ll probably get links to Microsoft’s site where you can see the syntax, and forums and blogs with examples, including ways to fix that particular error.
Your data had six columns, which were an index, date, time, and three sets of Y values. My most recent code assumes an index, date, and time, plus an indeterminate number of Y value columns. The code inserts a column between the time and the first Y value column, and puts a formula that computes date-time. Then it uses everything from this column (column 4) to the last column in the chart. With the first row of column 4 blank, Excel determines that column 4 is not like the rest, so it will use this column for X values, and the rest (however many there are) as Y values. I tested with 6 initial columns (3 Y value columns), but as long as there is at least one Y value column, it will work fine.
I didn’t know OWC was still a thing. But instead of using some embedded chart control, why not use Excel’s native charts? Build it and display it right in the workbook, very straightforward. I did that above by setting a variable to the chart, then creating it using syntax that you can find by recording a macro or digging through the Object Browser (a part of the VBA editor).
Here are a few articles on this blog that might help with VBA:
How To: Record Your Own Macro
How To: Fix a Recorded Macro
VB Editor Settings
There are a lot of chart VBA examples buried here.
Larry Kiser says
Thanks for seemingly working at all hours on this. Was the first comment cut off?
myChart.SetSourceData Source:=rChartData?,pre>
That was the last line.
My error message is Run Time error ‘1004’ Application defined or object-defined error. And it does not point to a line.
I have this code in the workbook-open section. I have removed all code except for what is below and I get the same error. It sounds like once I get the 4th column inserted and working then I could record a macro of me adding a new sheet, then adding graph, etc. Which I will try to walk through.
I dont know enough to know why the OWC was chosen, other than previous examples of how to do something like this and following along. One challenge I have is that the data could be 40k rows or could be 20k rows so I am concerned about formatting, chart size etc. With the OWC it was able to handle that dynamically. It is my hope that the recorded macro could be the same.
Private Sub Workbook_Open()
Dim sCSVFullName As String, sWbkFullName As String
sCSVFullName = “C:\Users\llk\Desktop\Trend_data_Peltier.csv”
Workbooks.Open sCSVFullName
sWbkFullName = Left$(sCSVFullName, Len(sCSVFullName) – 4) & “.xlsx”
ActiveWorkbook.SaveAs sWbkFullName, xlWorkbookDefault
Dim nRows As Long
Dim nCols As Long
Dim rUsed As Range
ActiveSheet.Columns(4).Insert Shift:=xlToRight
Set rUsed = ActiveSheet.UsedRange
nRows = rUsed.Rows.Count
nCols = rUsed.Columns.Count
Dim rDateTime As Range
Set rDateTime = rUsed.Columns(4).Offset(1).Resize(nRows – 1)
‘ this keeps a blank cell in top row to aid in charting
rDateTime.Formula = “=B2+C2”
rDateTime.NumberFormat = “m/d/yyyy h:mm”
End Sub
Larry Kiser says
My end goal is for an inexperienced user to click on a file that reads the csv from a usb stick and then present a graph. My specialty is PLC and electrical engineering. But I have been tasked with taking the output of a panelview display and graphing accordingly.
Jon Peltier says
Larry –
I try to do some work on my blog or on forums in the evenings. That post wasn’t cut off, but the “?,pre>” was some html gibberish that I’ve since fixed.
Generally when an error pops up, even one with as nondescriptive a message as that one, a line of code is highlighted.
The code you’ve retained imports the CSV file, saves it as an Excel workbook, then inserts the Date-Time column. There is nothing that inserts or populates the chart. The last bit of code I provided could be inserted before the
End Sub.I didn’t ask what version of Excel you are using. The code I posted for inserting a chart:
works in Excel 2013 and 2016, but would crash in 2007 and 2010. For those (and also later versions) you could use:
Also, up until Excel 2010 there was a limit of 32000 points per series, so the data import would have to include a way to chart only the latest 32000 points. I believe it was Excel 2013 that removed this limit.
I’m halfway done with my simpler CSV tutorial, but I had a couple interesting blue screens on that laptop while working on the code (I had to write and rewrite the first parts of it a few times, until I remembered to save early and often). I’ll start very simply, then include data sets that (a) have unused columns, and (b) require a little pre-processing before charting (like calculating values or limiting rows).
There are other alternatives, such as starting with a file that has dummy data in it, and inserting the CSV data, so the chart is already nicely formatted. Also instead of hard-coding the file name, this tutorial shows how to use a dialog to let the user browse to a file (
Application.GetOpenFilename).Larry Kiser says
I am using 2013. I was trying to remove lines to see if I could ascertain the location of the error but even at a minimum previously posted, I would get that error. I found/used a different method to insert the column which works.
Works: I commented out your line to insert the column and added the two other columns.
Private Sub Workbook_Open()
Dim sCSVFullName As String
Dim sWbkFullName As String
sCSVFullName = “C:\Users\llk\Desktop\For Sean\Trend_data_Peltier.csv”
Workbooks.Open sCSVFullName
sWbkFullName = Left$(sCSVFullName, Len(sCSVFullName) – 4) & “V1” & “.xlsx”
ActiveWorkbook.SaveAs sWbkFullName, xlWorkbookDefault
Dim nRows As Long
Dim nCols As Long
Dim rUsed As Range
Columns(“D:D”).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
‘ActiveSheet.Columns(4).Insert Shift:=xlToRight
Set rUsed = ActiveSheet.UsedRange
nRows = rUsed.Rows.Count
nCols = rUsed.Columns.Count
Dim rDateTime As Range
‘Set rDateTime = rUsed.Columns(“D:D”).Offset(1).Resize(nRows – 1)
‘ this keeps a blank cell in top row to aid in charting
‘rDateTime.Formula = “=B2+C2”
‘rDateTime.NumberFormat = “m/d/yyyy h:mm”
End Sub
Doesnt Work: (and gives the previously discussed error)
Private Sub Workbook_Open()
Dim sCSVFullName As String
Dim sWbkFullName As String
sCSVFullName = “C:\Users\llk\Desktop\For Sean\Trend_data_Peltier.csv”
Workbooks.Open sCSVFullName
sWbkFullName = Left$(sCSVFullName, Len(sCSVFullName) – 4) & “V1” & “.xlsx”
ActiveWorkbook.SaveAs sWbkFullName, xlWorkbookDefault
Dim nRows As Long
Dim nCols As Long
Dim rUsed As Range
‘Columns(“D:D”).Select
‘Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Columns(4).Insert Shift:=xlToRight
Set rUsed = ActiveSheet.UsedRange
nRows = rUsed.Rows.Count
nCols = rUsed.Columns.Count
Dim rDateTime As Range
‘Set rDateTime = rUsed.Columns(“D:D”).Offset(1).Resize(nRows – 1)
‘ this keeps a blank cell in top row to aid in charting
‘rDateTime.Formula = “=B2+C2”
‘rDateTime.NumberFormat = “m/d/yyyy h:mm”
End Sub
When I try to uncomment out rows to add the next step I get the error when enabling the following line
‘Set rDateTime = rUsed.Columns(4).Offset(1).Resize(nRows – 1)
So it seems reasonable to me that this line is what is causing me fits.
Is there anything I need to ensure I have added/installed to execute this?
Ill try to wait for your new article but I will keep working on it.
Thanks again.
Dion says
Hi thanks for your sharing. I would like to know is it possible for me to add some more features to the code like using “=INT(TEXT(D3-D2,”[s]”))” and “=MID(A3:A900,12,8)” in manual worksheet ?
Jon Peltier says
Dion –
Do you want the code to insert these formulas into cells of the worksheet? Or do you want the code to apply the formulas to data, then simply insert the result into cells?
Dion says
Hi Jon, thanks for the reply. I would like the code to apply the formulas to data, then simply insert the result into cells. My data will vary in every different csv file, i would like to use those formula (countif, moving average) to process the data then plot a line graph.
Jon Peltier says
Is the processing always the same? Would you rather keep the original data in the first several columns, then use the formulas in more columns, then plot these?
Dion says
Hi jon, the processing is always the same. i would like to keep the original data at their original column, then use formulas in new columns. I wish to have the processed result showed in the converted xlsx file from csv file. Also, I would like to keep the option where user can choose x and y axis and what graph to plot, it is much more flexible. Basically the code example u provide is simply fantastic, i just wanna add some formula in it, yet i really don’t know where to start.
Dion says
Option Explicit
Sub OpenCSVFileAndPlotData()
Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
Dim wb As Workbook, ws As Worksheet, rng As Range, vRng As Variant
Dim rCht As Range, cht As Chart, srs As Series
Dim iRows As Long, iCols As Long, iRow As Long, iCol As Long, LastRow As Long
Dim sTemp As String
Dim vChartData As Variant
Dim bFirstRowHeaders As Boolean
Dim myChartType As XlChartType
Dim vX As Variant, vY As Variant
Dim iX As Long, iY As Long, iSrs As Long
Dim nX As Long, nY As Long, nSrs As Long
Dim frmChartFromCSVFile As FChartFromCSVFile
‘ 1. Get CSV file name
sCSVFullName = Application.GetOpenFilename(“CSV Files (*.csv), *.csv”, , “Select a CSV file”, , False)
‘ 2. Open CSV file
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook
‘ 3. Save as workbook
LastRow = Cells(Rows.Count, “A”).End(xlUp).Row
Range(“D1”).Select
ActiveCell.FormulaR1C1 = “=COUNTIF(C[-3],RC[-3])”
”Range(“D1″).Select
”Selection.AutoFill Destination:=Range(“D1:D898”)
Selection.AutoFill Destination:=Range(“D1:D” & LastRow)
Range(“D1:D” & LastRow).Select
Range(“E1”).Select
ActiveCell.FormulaR1C1 = “0”
Range(“E2”).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = “=R[-1]C+1/R[-1]C[-1]”
Range(“E2”).Select
Selection.AutoFill Destination:=Range(“E2:E” & LastRow)
Range(“E2:E” & LastRow).Select
Range(“F1”).Select
ActiveCell.FormulaR1C1 = “=RC[-4]/10”
Range(“F1”).Select
Selection.AutoFill Destination:=Range(“F1:F” & LastRow)
Range(“F1:F” & LastRow).Select
Range(“G1”).Select
Application.Run “ATPVBAEN.XLAM!Moveavg”, ActiveSheet.Range(“$F$1:$F$” & LastRow), _
ActiveSheet.Range(“$G:$G”), 10, False, False, False
sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, “.”) – 1)
sWbkFullName = sFileRoot & “.xlsx”
wb.SaveAs sWbkFullName, xlWorkbookDefault
‘ 4. Parse file
Set ws = wb.Worksheets(1)
Set rng = ws.UsedRange
vRng = rng.Value2
iRows = rng.Rows.Count
iCols = rng.Columns.Count
” info to display: column number, first few rows of column
ReDim vChartData(1 To iCols, 1 To 2)
For iCol = 1 To iCols
vChartData(iCol, 1) = iCol
sTemp = “”
For iRow = 1 To 4
If iRow > iRows Then Exit For
sTemp = sTemp & vRng(iRow, iCol) & “, ”
Next
sTemp = Left$(sTemp, Len(sTemp) – 2)
vChartData(iCol, 2) = sTemp
Next
‘ 5. Show dialog (get chart type, X values, Y values)
Set frmChartFromCSVFile = New FChartFromCSVFile
With frmChartFromCSVFile
” pass in information we know
.ChartData = vChartData
.Show
myChartType = .ChartType
bFirstRowHeaders = .FirstRowHeaders
vX = .Xcolumns
vY = .YColumns
End With
Unload frmChartFromCSVFile
‘ 6. Draw chart
” define some series parameters
If IsEmpty(vX) Then
nX = 0
Else
nX = UBound(vX, 1) + 1 – LBound(vX, 1)
End If
nY = UBound(vY, 1) + 1 – LBound(vY, 1)
nSrs = nY
If nX > nY Then nSrs = nX
If bFirstRowHeaders Then
Set rCht = rng.Offset(1).Resize(iRows – 1)
Else
Set rCht = rng
End If
” select blank cell before inserting chart
rng.Offset(iRows + 1, iCols + 1).Resize(1, 1).Select
Set cht = ws.Shapes.AddChart.Chart ” Excel 2007+
”Set cht = ws.Shapes.AddChart2.Chart ” Excel 2013 only
” chart type
With cht
.ChartType = myChartType
” add series
For iSrs = 1 To nSrs
Set srs = .SeriesCollection.NewSeries
With srs
‘ X values
If nX = 0 Then
‘ no X values specified
ElseIf nX = 1 Then
‘ all series share X values
.XValues = rCht.Columns(CLng(vX(0, 0)))
Else
‘ each series has unique X values
.XValues = rCht.Columns(CLng(vX(iSrs – 1, 0)))
End If
‘ Y values
If nY = 1 Then
‘ all series share Y values
.Values = rCht.Columns(CLng(vY(0, 0)))
Else
‘ each series has unique Y values
.Values = rCht.Columns(CLng(vY(iSrs – 1, 0)))
End If
‘ series name
If bFirstRowHeaders Then
If nSrs = nY Then
.Name = “=” & rng.Cells(1, CLng(vY(iSrs – 1, 0))).Address(True, True, xlA1, True)
ElseIf nSrs = nX Then
.Name = “=” & rng.Cells(1, CLng(vX(iSrs – 1, 0))).Address(True, True, xlA1, True)
End If
End If
End With
Next
End With
‘ 7. Save file
wb.Save
ExitProcedure:
End Sub
Hi Jon, I tried to edit your code, but I keep getting error 13: type mismatch at this line:
sTemp = sTemp & vRng(iRow, iCol) & “, ”
Can u help out ? what is wrong with my code?
Vivek says
Hi Jon,
Thank you so much for sharing your code. My apologies if this question sounds trivial. I have a small issue while using your code in my user form. I have created a user form which does multiple functions and have assigned a button in my User Form1 (Button name: “Generate Plot”) which should ideally open up the User Form you created on the click of the button (FChartFromCSVFile) by parsing the column names in the active workbook. I have assigned your code from the module to the “Generate Plot” button & have imported your user form. But when I do this, I get “Run Time Error – 1004, Application defined or object defined error” & it highlights to the following line of the code ” Set frmChartFromCSVFile = New FChartFromCSVFile”. Any feedback on this issue will be very helpful.
To brief it up: I want to do the same function as your tutorial does but assign a button in a user form instead of directly assigning a button in a workbook. Thank you so much.
Jon Peltier says
Vivek –
Add a CommandButton to the form. It’s default name is CommandButton1 (or whatever number is next) but you may want to give it a better name, like btnLaunchCSVPlotter.
Double click on the button in the form, and you will be directed to a new procedure stub in the UserForm’s code module:
or
Insert the name of the procedure you want to run in the procedure:
or
Vivek says
Hi Jon,
Thank you for the feedback. Let me explain the situation, I tried the changes on your workbook (file downloaded from this website) it works fine & no issues seen while assigning a button to a new user form. However, when I try to create the same functionality from the scratch in a new workbook (creating a new .xlsm file & assigning the named range, modules & user form) I get the same error code & points me to the same line as mentioned in the previous conversation. I am not sure for this reason. But I have a workaround where I took the two user forms that I created & imported then to your workbook and that fixes the issue. Still, I am curious to know why it works only in the workbook downloaded from here & not in a new workbook created from the scratch, Also, is it possible to generate the plots in a different worksheet instead of generating on the same worksheet? Like when we hit the OK button, a sheet adds up & shows the plot. This will actually help me in exporting this to the PPT. Thank you very much for your help.
Jon Peltier says
Hmmm, all I can say is, make sure everything is spelled right and is in the right place, on the right form, and all variables are properly declared. Run-time error 1004 doesn’t mean much; the number is used for errors not covered by other numbers, and the description is next to useless.
Sergio says
Hey. I´m amateur at this and i have a problem with my code.
1) Firts, i have a correct code like of this post (get csv, open it, save as xlsx and do a chart with the source data):
Sub Simple_CSV_Plotter()
Dim sCSVFullName As String, sWbkFullName As String, sFileRoot As String
Dim wb As Workbook, ws As Worksheet
Dim sTemp As String
‘ 1. Get CSV file name
sCSVFullName = “X.csv”
‘ 2. Open CSV file
Workbooks.Open sCSVFullName
Set wb = ActiveWorkbook
‘ 3. Save as workbook
sFileRoot = Left$(sCSVFullName, InStrRev(sCSVFullName, “.”) – 1)
sWbkFullName = sFileRoot & “.xlsx”
wb.SaveAs sWbkFullName, xlWorkbookDefault
‘ 4. Parse file
Set ws = wb.Worksheets(1)
‘ 5. Insert and populate chart
With ws.Shapes.AddChart.Chart ” Excel 2007+
”With ws.Shapes.AddChart2.Chart ” Excel 2013 only
” chart type
.ChartType = xlLineMarkers
” chart data
.SetSourceData Source:=ws.UsedRange
End With
‘ 6. Save file
wb.Save
ExitProcedure:
End Sub
2) I don´t know how i can insert the chart created inside a cell and news stop, copy this cell with the chart and paste in original xlsx document. Or better, only copy the chart created and paste.
‘Sub Simple_CSV_Plotter_2()
‘7. Select chart of new file
‘Woorkbooks (“X.xlsx”)
‘.Worksheets(“Sheet1”).Chart
‘.Copy Woorkbooks(“C:\Users\sergi\Documents\Cursos GD\Criptos\Teoria Cripto_v2.xlsm”).Activate
‘ActiveWorkbook.Worksheets(“Y.xlsx”).Select
‘ActiveSheet.Paste
Please help!
Jon Peltier says
I think the easiest way to change the code is as follows. Instead of saving the CSV file as a new Excel Workbook, insert it as a new worksheet into the active workbook. Then do the work on this worksheet.
It should look something like this: