Upcoming 2017 Conferences and Training

There are several exciting conferences and other training activities coming up in the next few months. I will be participating in the first two 2017 conferences listed below: my third Amsterdam Excel Summit, and my second tour of Australia and New Zealand.


The Fourth Annual Amsterdam Excel Summit and Masterclass

The Excel Data Analysis And Reporting Masterclass, April 18, 2017

Join four leading Excel experts and learn about advanced data analysis and reporting techniques. Gašper Kamenšek, Oz Du Soleil, Jon Peltier, and David Hoppe will guide you through working with data, building a model, and designing a dashboard.

The Amsterdam Excel Summit, April 19, 2017

Several Excel experts and MVPs will present sessions on data analysis and modeling, Power Query, Power Pivot, Power BI, Excel Tables (and pivot tables and charts), and Excel extensibility (VBA, .Net, VSTO, JavaScript).

Register here for these events.


Unlock Excel

Powered for Accounting and Finance

For the second year in a row, the Excel MVPs tour Australia and New Zealand with a series of conferences aimed at accounting and finance professionals. Sponsored by CPA Australia, this year’s tour expands to four cities.

The experts are Liam Bastick, Roger Govier, Ingeborg Hawighorst, Jon Peltier, Ken Puls, Mynda Treacy, and Chandoo (in Auckland only). Topics include Modeling, Charts and Dashboards, Power Query, Power Pivot, Power BI, Spreadsheet Best Practices, and more. Microsoft PMs will also be present, sharing insights into new Excel developments.

Melbourne (1-2 May 2017), Brisbane (4-5 May 2017), Sydney (8-9 May) and Auckland (11-12 May 2017)


Power BI Dashboards for Excel Users

Power BI Webinar

Free Online Webinar by Mynda Treacy of MyOnlineTrainingHub

Presented on several dates: 14 February through 20 February, 2017.

Power BI is an amazing standalone dashboard tool From Microsoft. It lets you build powerful interactive dashboards and share them on any device.

Power BI plays nicely with Excel, allowing you to use Excel charts and tables, and it includes the same Power Query and Power Pivot tools available in Excel.

This webinar shows the power and benefits of Power BI while building two example dashboards. Sign up now.


Data to Dashboards Boot Camp

Data to Dashboards Power BI Boot Camp

Three-Day Boot Camp by Ken Puls of Excel Guru.

Hit the ground running with this intensive, three-day, hands-on boot camp covering all aspects of Power BI, including:

  • Data sourcing and transformation
  • Data modelling and DAX measures
  • Working with data visualizations
  • Publishing Power BI solutions
  • Sharing dashboards with others

Vancouver BC
February 22-24 2017
Excelguru.ca


Power Query Workshop

Live Online Workshop by Ken Puls and Miguel Escobar

This live online workshop covers basic and advanced topics of Power Query and the M Language to help you build your own data solutions.

  • 1 Free Book (eBook version)
  • 2-Day Course (4 hours each)
  • 1-Q&A Session (4 hours)
  • Top Quality Instructors
  • 6 Hands-on Labs
  • HD recordings of all sessions

March 1, 2, and 15, 2017


 

Peltier Tech Charts for Excel

Open CSV File and Chart CSV Data

Often data becomes available to us in formats other than Excel workbooks, such as CSV data files. A CSV (Comma-Separated-Value) file is a simple text file, with each row being a record, and the field values within each record are separated by commas; often the first row contains field names. Excel can open CSV files readily enough, and then you can work with the data like any worksheet data. But if you receive a lot of these files, it might be nice to have a VBA routine that will handle them for you.

A while back I wrote a couple of posts that showed how to plot data from CSV files (see links at end of this article). A reader had some issues implementing those techniques. They were complex approaches, one contained a dialog for the user to select columns to plot and the other was a full-blown add-in. I realized that I didn’t have a good introductory tutorial for opening and processing CSV files, so here it is.

I’ll start with a simple routine that opens a CSV file, saves it as an Excel workbook, then plots all the data in the file, letting Excel use whatever defaults it sees fit. Then I’ll show a few variations, to help you figure out how to work with your own special data.

Here is a zipped Excel workbook that contains all of this code, with buttons on the worksheet to run each routine. The zip file also contains a folder with a number of dummy CSV files.
VBA_Routines_to_Process_CSV_Files.zip


Simple CSV File – Open and Chart All CSV Data

I’ll use this simple CSV data. It has headers in the first row, X values in the first column, and Y values in the next three columns. Of course it doesn’t look like columns, since the values are separated by commas. But when opened in Excel, the data will be placed into columns.

Simple CSV Data

The code has to do these things:

  • Open the CSV file
  • Insert an XY Scatter chart
  • Populate the chart with the applicable data
  • Save the file with its chart as an Excel workbook

And here’s the simple code:

Sub CSV_OpenAndChart_1()
  ' declare variables
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim cht As Chart

  ' hard-coded CSV filename
  sCSVFile = "C:\csv_data\csv_data_1.csv"
  ' workbook name: strip off everything after last "." and add "xlsx" extension
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet

  ' insert and populate chart
  Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart
  cht.SetSourceData Source:=ws.Range("$A$1:$D$8"), PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = ws.Range("$A$1:$D$8").Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

End Sub

After the code runs, here’s what the Excel worksheet looks like. The data up at the top is no longer comma-separated, it’s arranged into rows and columns. And the chart is right below the end of the data.

Worksheet after processing simple CSV file


Improvements

Before you even customize the code to accommodate any special features of your data, there are a couple of tricks that you ought to know about.

FileDialogFilePicker

In our procedure, the file name is hard-coded. I’m sure I don’t have to say why that’s a bad idea, except to say that you’re making a huge assumption that you’ve typed in the only file name that you’re ever going to need. And if the file isn’t there, well…

Error: File not Found

And you can’t just tell your users to type in a new file name in the code, because you can’t trust that they won’t break the code.

Using the file dialog isn’t much more difficult than hard-coding the file name. You need a few more variables and a few more lines of code:

  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_1.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    sCSVFile = fdSelected(1)
  End With

  ' clean up
  Set fdSelected = Nothing
  Set fd = Nothing

  Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

UsedRange

In the code, we can assign the value of a range variable as follows:

  Dim rUsed As Range
  Set rUsed = ws.UsedRange

The used range of a worksheet is the rectangular region that encloses all of the data. In our example, it’s A1:E8. Using UsedRange means I don’t need to know the address of the data range, and I don’t need to count how many rows or columns are in the data, I can just pass the used range to the chart.

Suppose my CSV data had more columns. My code using UsedRange doesn’t care, since it uses the whole used range of data in the file. The following starts with a CSV file with six columns instead of four, and all of the Y value columns are plotted.

Worksheet after processing simple CSV file

AddChart2

In the procedure above I used the following to insert the chart:

  Set cht = ws.Shapes.AddChart2(240, xlXYScatterLines).Chart

How did I know to use AddChart2? I can never remember the exact syntax, so I opened the CSV file and recorded a macro while I inserted the chart, and this is what the Macro Recorder recorded. That’s fine, AddChart2 gives us the nicely formatted charts which were introduced in Excel 2013. The 240 inside the brackets tell Excel to apply style 240, and of course xlXYScatterlines is the chart type. But Excel 2007 and 2010 don’t understand AddChart2: you’ll get a compile error, and Excel won’t even run any of the procedure. For these earlier versions of Excel, we need to use this code:

  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart

No style number, and you end up with the uglier default formatting, Even in Excel 2013 or 2016. For reference, the output of Excel 2007 and 2010’s AddChart is shown as the top chart below, with the output of Excel 2013 and 2016’s AddChart2 as the bottom chart below.

Default 2007 and 2010 chart above, and 2013 and 2016 chart below

So how do you write code that does not crash in Excel 2007 and 2010, but still provides the nice chart styles of Excel 2013 and 2016? Excel 2013 and 2016 still can use AddChart, even though the preferred syntax is AddChart2, but you’ll get the uglier old formats. You can’t apply style 240 in the earlier releases, because it will not be recognized. So you need to test for Excel version, and apply the style in later versions.

  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If

Application.Version returns a text string, for example, “12.0” for Excel 2007 or “16.0” for Excel 2016. We need to convert the string to a numerical value; this happens automatically if your system uses a period as a decimal separator, but it misbehaves if it expects a comma. Val(Application.Version) returns the numerical value, that is, 12 or 16.


Simple CSV File – Open and Chart All CSV Data – Enhanced Code

Here’s our simple procedure from before, incorporating the enhancedments described above:

Sub CSV_OpenAndChart_1A_FileDialog_AddChart()
  ' variables for File Dialog
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String

  ' declare variables
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_1.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rUsed, PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

CSV Data Structure: Skip First Column

Input data is not always (not usually, in fact) laid out for a simple input and chart routine. What if the first column of the CSV file is some kind of index or description. If we try to plot it, we will get a messy chart. So we want to skip the first column; using similar logic you can skip the first three columns, or the third column, or whatever data you don’t want in your chart.

Here’s a CSV file like the one used above, but the first column is an index.

CSV Data with Index Column

We’ll build on the previous routine, with its file dialog and AddChart/ChartStyle approach, and we’ll modify the chart data range.

Sub CSV_OpenAndChart_2A_SkipFirstColumn()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' variables to help modify chart data range
  Dim nRows As Long, nCols As Long
  Dim rChart As Range

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_2.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' modified data range
  nRows = rUsed.Rows.Count
  nCols = rUsed.Columns.Count
  Set rChart = rUsed.Offset(, 1).Resize(, nCols - 1)

  ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

After the code is run, the new workbook looks like the following. The chart is selected, and you can see that the first column of data is not highlighted as part of the chart’s data range.

Worksheet after processing CSV file, plotting all but first column


CSV Data Structure: Combine Date and Time Columns into Chart-Friendly Date-Time

The previous data treatment was pretty easy. What if, instead of a nice column of X values, we have a column of dates and another column of times.

To review quickly, Excel treats a date as a whole number (the number of days since 1-Jan-1900, give or take an erroneous leap day, but that’s another story, and it’s not even Microsoft’s fault), and it treats a time as a fraction (the fraction of 24 hours that has elapsed since the previous midnight). You can’t plot by separate date and time values, but if you add them together you get a date-time value that can be treated as a numerical value on the X axis of a scatter chart.

So we need to insert a new column, and add the dates and times to get chart-ready date-time values. Here is a sample, shown for simpliciy in a worksheet rather than in a text file reader. Like the previous data set, the first column is an index which will be ignored by the chart. Columns B and C are the dates and times which must be combined in a new column inserted after column C.

Data set with index column to be ingored, and date and time columns to be combined

Sub CSV_OpenAndChart_3A_CombineDateTime()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range
  Dim cht As Chart

  ' variables to help modify chart data range
  Dim nRows As Long, nCols As Long
  Dim rChart As Range

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\csv_data_3.csv"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = False
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
  End With

  sCSVFile = fdSelected(1)
  sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

  ' open CSV file
  Workbooks.Open sCSVFile
  ' assign variables
  Set ws = ActiveSheet
  Set rUsed = ws.UsedRange

  ' insert column to combine date (column 2) with time (column 3)
  ws.Columns(4).Insert xlToRight
  Set rUsed = ws.UsedRange
  nRows = rUsed.Rows.Count
  nCols = rUsed.Columns.Count
  With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
    .Formula = "=B2+C2"
    .NumberFormat = "m/d/yyyy h:mm"
  End With
  Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
  Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
  If Val(Application.Version) > 14 Then
    cht.ChartStyle = 240
  End If
  cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
  cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

  ' move chart to left, under data, and make it smaller
  With cht.Parent
    .Left = 10
    .Top = rUsed.Height + 10
    .Width = 275
    .Height = 200
  End With

  ' save as Excel Workbook
  ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Worksheet after processing complicated data set


Multiple CSV Files, Processed Separately

If you get a dozen CSV files all at once, you could run the code a dozen times, selecting each file in turn. But wouldn’t it be better to select all the files in one trip to the file dialog, then let the code run all of the files?

You may have noticed the AllowMultiSelect property of the file dialog, and that we set its value to False. We can set this property to True, and the dialog will allow us to select multiple files using the Shift or Ctrl key as we’re used to. Each selected file will be a member of the file dialog’s SelectedItems. We can wrap the file-processing code in a For-Next loop to process each file in turn.

Note that if you select one file in the dialog, this routine will process that one file just fine.

Sub CSV_OpenAndChart_4A_MultipleFiles()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim ws As Worksheet
  Dim rUsed As Range, rChart As Range
  Dim cht As Chart
  Dim nRows As Long, nCols As Long

  ' file counters
  Dim iFile As Long, nFiles As Long

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\"
  sCurDir = CurDir
  
  ' create the dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = True
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    nFiles = fdSelected.Count
  End With

  ' do all the files
  For iFile = 1 To nFiles
    sCSVFile = fdSelected(iFile)
    sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

    ' open CSV file
    Workbooks.Open sCSVFile
    ' assign variables
    Set ws = ActiveSheet
    Set rUsed = ws.UsedRange

    ' insert column to combine date (column 2) with time (column 3)
    ws.Columns(4).Insert xlToRight
    Set rUsed = ws.UsedRange
    nRows = rUsed.Rows.Count
    nCols = rUsed.Columns.Count
    With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
      .Formula = "=B2+C2"
      .NumberFormat = "m/d/yyyy h:mm"
    End With
    Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
    Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
    If Val(Application.Version) > 14 Then
      cht.ChartStyle = 240
    End If
    cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

    ' move chart to left, under data, and make it smaller
    With cht.Parent
      .Left = 10
      .Top = rUsed.Height + 10
      .Width = 275
      .Height = 200
    End With

    ' save as Excel Workbook
    ActiveWorkbook.SaveAs sWorkbook, xlWorkbookDefault
    ActiveWorkbook.Close
  Next
Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Multiple CSV Files, Combined Into One Output Workbook

The final variation I’ll talk about here is processing multiple CSV files (and it works with just one CSV file), and placing each output worksheet into one output workbook; the worksheet tabs will reflect the names of the input CSV files. We’ll use an additional file dialog, the msoFileDialogSaveAs dialog, to ask the user for a filename for this output workbook.

Sub CSV_OpenAndChart_5A_CombineMultipleFiles()
  ' declare variables
  Dim fd As FileDialog
  Dim fdSelected As FileDialogSelectedItems
  Dim sInitial As String
  Dim sCurDir As String
  Dim sCSVFile As String
  Dim sWorkbook As String
  Dim wbOutput As Workbook
  Dim ws As Worksheet
  Dim rUsed As Range, rChart As Range
  Dim cht As Chart
  Dim nRows As Long, nCols As Long
  Dim iFile As Long, nFiles As Long

  ' initial folder to open in dialog
  ' if file name included, it is preselected
  sInitial = "C:\csv_data\"
  sCurDir = CurDir
  
  ' create the file dialog
  Set fd = Application.FileDialog(msoFileDialogFilePicker)
  With fd
    ' apply dialog settings
    .AllowMultiSelect = True
    .InitialFileName = sInitial
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Title = "Select CSV File"

    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure

    ' get the selected file
    Set fdSelected = .SelectedItems
    nFiles = fdSelected.Count
  End With

  ' open output workbook
  Set wbOutput = Workbooks.Add

  ' do all the files
  For iFile = 1 To nFiles
    sCSVFile = fdSelected(iFile)
    sWorkbook = Left$(sCSVFile, InStrRev(sCSVFile, ".")) & "xlsx"

    ' open CSV file
    Workbooks.Open sCSVFile
    ' assign variables
    Set ws = ActiveSheet
    Set rUsed = ws.UsedRange

    ' insert column to combine date (column 2) with time (column 3)
    ws.Columns(4).Insert xlToRight
    Set rUsed = ws.UsedRange
    nRows = rUsed.Rows.Count
    nCols = rUsed.Columns.Count
    With rUsed.Columns(4).Offset(1).Resize(nRows - 1)
      .Formula = "=B2+C2"
      .NumberFormat = "m/d/yyyy h:mm"
    End With
    Set rChart = rUsed.Offset(, 3).Resize(, nCols - 3)

    ' syntax for 2007-2010-2013-2016
    Set cht = ws.Shapes.AddChart(xlXYScatterLines).Chart
    If Val(Application.Version) > 14 Then
      cht.ChartStyle = 240
    End If
    cht.SetSourceData Source:=rChart, PlotBy:=xlColumns

    ' format axis tick labels
    cht.Axes(xlCategory).TickLabels.NumberFormat = "m/d"

    ' move chart to left, under data, and make it smaller
    With cht.Parent
      .Left = 10
      .Top = rUsed.Height + 10
      .Width = 275
      .Height = 200
    End With

    ' put into output workbook
    ws.Move After:=wbOutput.Worksheets(wbOutput.Worksheets.Count)
  Next

  ' delete blank first worksheet
  ' display alerts -> false to hide warning
  Application.DisplayAlerts = False
  wbOutput.Worksheets(1).Delete
  Application.DisplayAlerts = True

  ' create new file dialog
  Set fd = Application.FileDialog(msoFileDialogSaveAs)
  With fd
    ' apply dialog settings
    .InitialFileName = sInitial
    .Title = "Enter Output Workbook Name"
    
    ' show the dialog
    ' .Show returns -1 if Open, 0 if Cancel
    If .Show = 0 Then GoTo Exit_Procedure
    
    ' get the selected filename
    Set fdSelected = .SelectedItems
  End With

  sWorkbook = fdSelected(1)
  wbOutput.SaveAs sWorkbook, xlWorkbookDefault

Exit_Procedure:
  ChDrive sCurDir
  ChDir sCurDir

End Sub

Other Examples Using CSV Files on Peltier Tech Blog

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.

VBA: An Advanced Add-in to Open a CSV File and Create Chart with Data Specified by User
This tutorial shows development of an advanced VBA add-in to open a CSV file, save it as an Excel workbook, and let the user select which data to plot.

 

Peltier Tech Charts for Excel

Excel Books

Often when I’m working with someone, showing off my Excel tricks, I am asked, “How do you know all that stuff?” And the answer is simple: I’ve been using Excel for around 25 years, Excel charts for all that time, and Excel VBA for 20 years. There’s a combination of natural curiosity (“what does this button do” and “how do I do that”), and reading manuals (in the days before online help), and becoming good at Google. And there are a thousand Excel books that claim to teach you everything about Excel.

I’ve decided to put together a list of good Excel books, to make it easy for people to find these resources. This is not a comprehensive list of all Excel books. Instead, it is a list of some of the books that I have found helpful. I know most of the authors of these books, but I also know authors of books not included here. Books not listed are not omitted because they are bad, necessarily, but just because I am limiting the size of this article.

A couple authors may seem overrepresented here. Both are comprehensive in their approach, and they cover a range of topics and levels of difficulty. John “Mr Spreadsheet” Walkenbach has been writing about spreadsheets since before Excel was a thing, and for each version of Excel that is released, there is a family of Mr Spreadsheet books on an array of topics. You really can’t go wrong with one of the Mr Spreadsheet family of books. John doesn’t do all of the writing anymore, but he has enlisted knowledgeable co-authors to maintain the tradition. Bill “Mr Excel” Jelen runs the best Excel forum in the known universe and has his own popular series of Excel books. I know both of these experts, and I like their books. Like John, Bill also has used experts to help with the writing tasks.

I may expand this list in the future, maybe to add books to the existing categories, but mostly to add more categories. For example, I have not listed any books that discuss the new data technologies of Excel (Power Pivot etc.). For now I will concentrate on general Excel topics, VBA, and charting. Note that the links here are all Amazon Affiliate links, so if you purchase any of these Excel books, I will get a small commission.

General Excel Books

 

Excel 2016 Bible by John Walkenbach – This is the most comprehensive, and heaviest, Excel book you will find anywhere. It covers everything about Excel in extensive detail. Formulas, charting, data analysis, formatting, and programming.

Learn Excel 2007 through Excel 2010 From MrExcel by Bill Jelen – While not as extensive as the Excel Bible, this is a very good all-around resource.

Excel Formulas

 

Excel 2016 Formulas by Michael Alexander and Dick Kusleika – If the Excel Bible was so comprehensive, then why did John Walkenbach need a book about Excel formulas? This book covers formulas in more depth, formulas in defined Names, formulas in Conditional Formatting, financial formulas, array formulas, and user defined functions that use VBA.

Ctrl+Shift+Enter – Mastering Excel Array Formulas by Mike “Excel Is Fun” Girvin – Mike turns you on to the magic of array formulas in this book, with the same irreverent approach as you’ve seen in the popular ExcelIsFun YouTube channel.

Excel Pivot Tables

   

Excel 2016 Pivot Table Data Crunching by Bill Jelen and Michael Alexander – A comprehensive volume about building, formatting, and modifying pivot tables, with sections on Power Query for working with your input data, and VBA for automating the finished product.

Beginning PivotTables in Excel 2007: From Novice to Professional by Debra Dalgleish and Excel 2007 PivotTables Recipes: A Problem-Solution Approach by Debra Dalgleish – Two books from my favorite pivot table expert, the first with beginner to expert coverage, and the second with many helpful examples to get you started or get you over the hump.

Excel VBA Books

     

Excel 2016 Power Programming with VBA by Michael Alexander and Dick Kusleika – This book starts with recording macros, progresses through cleaning up recorded code, and leads to designing and writing your own code. It teaches about the tools built into the VB Editor, and covers topics like application and add-in development, VBA language features, and UserForms (custom dialogs).

Excel 2016 VBA and Macros by Bill Jelen and Tracy Syrstad – This is another extensive volume about using VBA in Excel. It covers macros, VBA-enhanced workbooks, and full-blown add-in applications.

RibbonX: Customizing the Office 2007 Ribbon by Robert Martin, Ken Puls, and Teresa Hennig – If your VBA project needs a user interface, this book is required reading. This is the book I still use the most; the cover has fallen off and a few pages are loose. It covers all you need to know about the XML code that drives the Microsoft Office ribbon, and about the VBA that supports the XML.

Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition) by Rob Bovey, Dennis Wallentin, Stephen Bullen, and John Green – When you are ready to create commercial quality add-ins, this book picks up from the others, and makes you a pro. Chapters on dictator applications, Windows APIs, error handling, controlling other Office programs, and localization give you things you can’t find anywhere else.

Excel Charting Books

     

Excel 2007 Charts by John Walkenbach and Excel 2013 Charts and Graphs by Bill Jelen – These books cover the basic charting topics, such as how to create a chart, chart types, chart data, and formatting. They work through advanced topics, such as statistics, pivot charts, and best practices. And they finish with detailed sections about automating charts with VBA.

Creating More Effective Graphs by Naomi Robbins – This is not an Excel book, but it provides great insights into how to present your data effectively.

Data at Work: Best practices for creating effective charts and information graphics in Microsoft Excel by Jorge Camoes – This is the Excel charting book that I would have liked to have written. Jorge has applied the best practices of data visualization to charting in Excel.

Excel Dashboards

   

Excel Dashboards and Reports for Dummies by Michael Alexander – Yes, a Dummies book, and no, I’m not crazy. This was the first book about Excel dashboards, and Mike did a good job of staying within the Dummies framework but providing lots of useful information.

Excel Dashboards and Reports by Michael Alexander and John Walkenbach – This advanced volume builds on Mike’s successful Dummies book, with more details about working with data and making effective visual reports.

Dashboards for Excel by Jordan Goldmeier and Purnachandra “Chandoo” Duggirala – In addition to applying data visualization practices and dynamic formulas and charts, this book digs into Power Query, Power Pivot, and the Excel Data Model to make robust and elaborate dashboards.

 

Peltier Tech Charts for Excel

Connect Two XY Series with Arrows

The Issue

If you have XY data from two different times, or under two different sets of conditions, you can connect points of one series (one time or condition) to another (another time or condition), to help show how the data evolves. For example, here are two series of data that I might want to connect together.

Series A and Series B to be connected by line segments

Several years ago I wrote Connect Two XY Series, which showed how to connect the points of one series to the corresponding points of another series. This approach used a third series with data from Series A and Series B, interwoven with blank cells.

Series A and Series B connected by line segments

I followed this up with Connect Two XY Series with Arrows (VBA), which showed how to connect the two series with arrows. In Excel 2003 and earlier, you had to use VBA to draw the arrows, and this article provided the VBA (and it’s reproduced below).

Series A and Series B connected by arrows, using VBA

This works nicely enough, but the arrows are not tied to the points, but only to the original positions of the points within the chart. So if you change the axes in the chart …

Series A and Series B no longer connected by arrows

… or change the size or shape of the chart …

Series A and Series B no longer connected by arrows

… the arrows no longer match up to the points.

You could always delete the misaligned arrows and rerun the VBA procedure, but what a pain.

I hinted in this second article that you could connect the points with arrows in Excel 2007, without using VBA but instead by formatting the third series that connected the first two series. But I never explained how. This article fills the gap.

Connect Two Series with Arrows, Without VBA

You need to keep the original Series A and B data in place, so you can still easily plot the original points. You need to get data into a new range, interweaving Series A with Series B.

Set Up The Data – Approach 1

To get the data into this alternating arrangement, start by pasting the data from Series A into a new range, then paste the data from Series B below it. Enter a set of index numbers into a column adjacent to this combined data, from 1 to the number of points in Series A, and from 1 to the number of points in Series B (which should be the same number of points). This is the first data block below.

Select the data, and sort by the column of digits you just entered. The data now has alternating Series A and B data (below right).

Sorted Data for Series A and B

Select the data and create a chart, or add it as a new series to the chart of Series A and Series B.

Series A and Series B connected with lines, but needing gaps at alternating segments

Hmmm, that’s almost what I want. But there are too many line segments. We need to eliminate half of them, shown as dashed lines below.

Series A and Series B connected with lines, showing where gaps are needed

We could format alternating line segments to be drawn with no line, but that’s mighty tedious after the first or second segment. Speaking from experience, because I had to format the dashed lines in the chart above, segment by segment.

As I’ve written about in Mind the Gap – Charting Empty Cells, the way we get a gap between points in a chart is to have a gap in the data, that is, blank cells between the points.

So let’s alter our protocol above, so that we start with the Series A data, then the Series B data below that, then an equal number of blank rows at the bottom, and include our index column (below left).

Sort by the index column, to get Series A and B data pairs, separated by blanks (below right).

Sorted Data for Series A and B and blanks

Now when we plot this data, we connect appropriate pairs of points, with gaps between points we do not want to connect.

Series A and Series B connected with lines, with gaps

Set Up The Data – Approach 2

Above we produced our data range by copying the data into a new range, and sorting the data appropriately. We could instead have stretched out the data first, then copied and pasted using the Skip Blanks option.

Here’s a copy of the Series A and B data, with two rows inserted between each point.

Series A and B data, expanded with blank rows

Copy the expanded Series A data, select the first cell of a new range, …

Series A data copied

… then paste.

Series A data pasted

Now copy the Series B data, select the cell below the first Series A point, …

Series B data copied

… and use Paste Special, and check the Skip Blanks option near the bottom of the dialog. Skip Blanks prevents blanks in the copied range from being pasted over data in the target range, so the Series A data is preserved.

Series B data pasted, skipping blanks to preserve Series A data

Now the data is ready to plot.

Make the Chart

Select the Series A data (shaded blue below), and create an XY Scatter chart. Then copy the Series B data (shaded orange), select the chart, and use Paste Special to add the data as a new series, with series in columns, Y values in the first column, series names in the first row. The resulting chart is shown below right.

Series A and Series B, to be connected with arrows

Now copy the A-B Interwoven data (below left), select the chart, and again use Paste Special to add this data as a new series, with series in columns, Y values in the first column, series names in the first row, to produce the chart shown below right. (You may have to format the new series to show lines and no markers)

Series A and Series B connected with line segments

Now you need to format the line segments of the new series. Select the series and press Ctrl+1 (numeral one) to open the Format Data Series task pane or dialog. Click the End Arrow Type dropdown, and select the desired arrow type. The options include three arrowheads, a circle, a diamond, and a plain line.

Format Data Series Lines - End Arrow Type

Now click the End Arrow Size dropdown, and choose an appropriate size for the arrowhead. There are combinations of three arrowhead lengths and widths.

Format Data Series Lines - End Arrow Size

Now the line segments have been transformed into arrows.

Series A and Series B connected with arrows

Unlike the VBA approach of the old tutorial, the arrows are tied to the points in Series A and Series B. So if you change the axes in the chart …

Series A and Series B still connected by arrows

… or change the size or shape of the chart …

Series A and Series B still connected by arrows

… the arrows still match up to the points.

The VBA

You don’t need to use VBA to connect points with arrows, but it is a good sample application of VBA to your charts. Just so you don’t have to go back to the earlier post, here is the VBA Procedure that connects the points with line segments with arrowheads.

Sub ConnectTwoXYSeriesWithArrows()
  Dim myCht As Chart
  Dim mySrs1 As Series
  Dim mySrs2 As Series
  Dim nPts As Long, iPts As Long
  Dim myBuilder As FreeformBuilder
  Dim myShape As Shape
  Dim iShp As Long
  Dim Xnode1 As Double, Ynode1 As Double
  Dim Xnode2 As Double, Ynode2 As Double
  Dim Xmin As Double, Xmax As Double
  Dim Ymin As Double, Ymax As Double
  Dim Xleft As Double, Ytop As Double
  Dim Xwidth As Double, Yheight As Double

  ' a chart with at least two series must be selected
  If ActiveChart Is Nothing Then
    GoTo ExitSub
  End If
  If ActiveChart.SeriesCollection.Count < 2 Then
    GoTo ExitSub
  End If

  Set myCht = ActiveChart
  Set mySrs1 = myCht.SeriesCollection(1)
  Set mySrs2 = myCht.SeriesCollection(2)
  nPts = mySrs1.Points.Count

  ' two series must have matching numbers of points
  If mySrs2.Points.Count <> nPts Then
    GoTo ExitSub
  End If

  ' remove any old connecting arrows
  For iShp = myCht.Shapes.Count To 1 Step -1
    If Left(myCht.Shapes(iShp).Name, 12) = "ArrowSegment" Then
      myCht.Shapes(iShp).Delete
    End If
  Next

  Xleft = myCht.PlotArea.InsideLeft
  Xwidth = myCht.PlotArea.InsideWidth
  Ytop = myCht.PlotArea.InsideTop
  Yheight = myCht.PlotArea.InsideHeight
  Xmin = myCht.Axes(xlCateogry).MinimumScale
  Xmax = myCht.Axes(xlCategory).MaximumScale
  Ymin = myCht.Axes(xlValue).MinimumScale
  Ymax = myCht.Axes(xlValue).MaximumScale

  For iPts = 1 To nPts
    ' first point
    Xnode1 = Xleft + (mySrs1.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode1 = Ytop + (Ymax - mySrs1.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' second point
    Xnode2 = Xleft + (mySrs2.XValues(iPts) - Xmin) * Xwidth / (Xmax - Xmin)
    Ynode2 = Ytop + (Ymax - mySrs2.Values(iPts)) * Yheight / (Ymax - Ymin)

    ' draw connecting line
    Set myShape = myCht.Shapes.AddLine(Xnode1, Ynode1, Xnode2, Ynode2)

    ' name and format shape as arrowhead
    With myShape
      .Name = "ArrowSegment" & CStr(iPts)
      With .Line
        ' USE YOUR FAVORITE FORMATS HERE
        .ForeColor.ObjectThemeColor = msoThemeAccent3
        .ForeColor.Brightness = -0.25
        .Weight = 1.5
        .EndArrowheadLength = msoArrowheadLong
        .EndArrowheadWidth = msoArrowheadWidthMedium
        .EndArrowheadStyle = msoArrowheadTriangle
      End With
    End With

  Next

ExitSub:
 
End Sub

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0