VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User

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 Sub

My 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 names 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 Explicit

If 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 Sub

The 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 = ActiveWorkbook

3. 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, xlWorkbookDefault

4. 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
  Next

5. 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 frmChartFromCSVFile

6. 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 With

7. Save file

Simple: save changes.

  wb.Save

UserForm

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 Explicit

If 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 Sub

To 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 Sub

When 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 Sub

The 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 Sub

Click 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 Sub

Click 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 Sub

We 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 Property

We 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 Property

The 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 Sub

UserForm 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 Sub

Workbook 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.

 

Peltier Tech Charts for Excel

Comments

  1. 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

  2. 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:

    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 = 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)
      
      ' 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

    You can download a workbook with this code and with a button on the worksheet that runs the code.

  3. 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

  4. 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

  5. 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):

    Dim sCSVFullName As String, sWbkFullName As String
    sCSVFullName = "C:\\Temp\MyData.csv"
    Workbooks.Open sCSVFullName
    sWbkFullName = Left$(sCSVFullName, Len(sCSVFullName) - 3) & ".xlsx"
    ActiveWorkbook.SaveAs sWbkFullName, xlWorkbookDefault

    2. Insert a new column

    Dim nRows As Long, 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

    3. Insert a simple formula to sum Date and Time

    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"

    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

    Dim rChartData As Range
    Dim myChart As Chart
    Set rChartData = Range(rUsed.Columns(4), rUsed.Columns(nCols))
    rChartData.Select
    Set myChart = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Chart
    myChart.SetSourceData Source:=rChartData
  6. 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.

  7. 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).

    Dim myChart As Chart
    Set myChart = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Chart

    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.

  8. 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

  9. 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.

  10. 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:

    Set myChart = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines).Chart

    works in Excel 2013 and 2016, but would crash in 2007 and 2010. For those (and also later versions) you could use:

    Set myChart = ActiveSheet.Shapes.AddChart(xlXYScatterLines).Chart

    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).

  11. 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.

Trackbacks

  1. […] article builds upon a previous post, VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User, which showed how to build the barest possible VBA procedure for opening a CSV file and making a […]

  2. […] VBA: A Simple Tool to Open a CSV File and Create Chart with Data Specified by User This tutorial shows development of a VBA program to open a CSV file, save it as an Excel workbook, and let the user select which data to plot. […]

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0