User Voice Fixes Pivot Table Default Settings

I’ve been using Pivot Tables in Excel for twenty years or more. They are one of the features that have made Excel such a killer program. Pivot Tables are easy to use after a bit of learning, and they’re fast and flexible. And the Pivot Table default settings used to be alright.

A few Excel versions ago, I believe in Excel 2007, Microsoft added some additional layouts to Pivot Tables, so in addition to the historic Tabular layout, you can now choose Compact or Outline. The problem is that they made Compact the default, and except for the simplest Pivot Tables, I never want to use Compact, I always want the Tabular layout. So right after I build a Pivot Table, I need to fix it. (This may have been Yet Another Reason to hate Excel 2007.)

There are a few other default settings that I need to correct these as well, though they were never as annoying as the Compact layout.

Let me illustrate the problem. I have a simple Table with the headings “Greek” (containing “Alpha”, “Beta”, or “Gamma”), “Latin” (containing “A”, “B”, or “C”), “Arabic” (containing (1, 2, 3, or 4), and “Values” (containing a random two-decimal-digit number between 3 and 8). When I insert a Pivot Table with Greek in the columns area, Latin and Arabic in the rows area, and Values in the values area, here is the out-of-the-box default Pivot Table.

Pivot Table Built-In Pivot Table Default Settings

What’s wrong with this Pivot Table?

  1. It’s in compact layout, which means all of the row pivot fields are compressed into one column, with field items indented according to their order in the rows area. The field names are not shown at the top of the field; instead there is a single label called Row Labels.
  2. There are subtotals, and I usually do not want subtotals.
  3. There are grand totals on rows and columns, and I usually want no subtotals at all, and if I do want subtotals, I usually only want column totals, or sometimes only row totals.
  4. The columns have all been autofitted, so the first two columns are very wide, and all columns have different widths. When the Pivot Table is refreshed, these widths are subject to change due to the autofitting.

So I have to go to the Pivot Table ribbon tab and fix the first three problems, and to Pivot Table Options to fix the column autofitting. Yeah, big deal, it’s only 30 seconds, but it’s 30 seconds each, and it’s easy to forget to fix them all, and anyway, it’s a pain.

For all those years I was merely annoyed about Excel’s Pivot Table default settings, Bill Mr Excel Jelen went further, and lobbied with every Excel user and every Microsoft Excel product team member to change them. For years this had no effect, other than he became well known within the Excel product group.

But then Microsoft introduced User Voice, an online forum that lets users suggest new features and improvements to existing features, and vote on other suggestions. Microsoft reads all of the suggestions, and those suggestions with the most votes are considered for inclusion in their product.

Bill submitted Allow users to set/choose defaults (via settings) for PivotTables – Layout (compact, tabular, classic, etc.), PT Options, etc., and it began piling up the votes.

Two weeks ago, the official Excel Team blog contained an article entitled PivotTables just got personal. Long story short, they’ve implemented Bill’s suggestion, and in a very flexible way. Instead of simply changing the Pivot Table default layout back to Tabular, they allow users to set all Pivot Table options to create a custom Pivot Table default. This feature is available to all Excel 2016 users with Office 365, as long as they have gotten the recent updates.

Here’s how to change your Pivot Table default settings. On Excel’s File tab, select Excel Options, and click on Data in the Excel Options dialog.

Data Tab of Excel Options Dialog

Click the Edit Default Layout button to open the Edit Default Layout button.

Edit Default Layout Dialog

Layout Import uses the settings of an existing Pivot Table as the new custom default layout (I happened to have cell S21 selected when I opened the dialog; this cell was not in any Pivot Table). You can start with that, or simply make any changes in the dialog. You can see that I’ve already fixed them all.

You can even click the Pivot Table Options button to access any settings in the entire Pivot Table Options dialog, like that pesky Autofit Column Widths setting.

Pivot Table Options Dialog

After correcting my Pivot Table default settings, when I insert a pivot table, here is the absolute first view of the result. I don’t need to change anything!

Pivot Table with Custom Pivot Table Default Settings

Thanks Microsoft, thanks User Voice, and thanks Mr Excel for his tireless whining lobbying to make this improvement to Excel. I hope this Pivot Table Default Layout feature becomes the model for how users interact with all Excel defaults in the future.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel – Demo Version

I’ve finally finished a demo version of Peltier Tech Charts for Excel 3.0. This trial version will allow potential users to try the software before they buy a license.

Waterfall Chart

In 2008 I launched my first commercial Excel add-in, Peltier Tech Waterfall Chart Utility. All it did was make waterfall charts. Over the years I made improvements and enhancements to the waterfall chart utility, and I introduced (and improved and enhanced) other chart types. In 2013 I merged them together into Peltier Tech Chart Utility 2.00, and added a bunch of other chart-related features. In 2015 I released a major upgrade, Peltier Tech Charts for Excel 3.0, which extended compatibility to Excel 2016 (including Office 365), allowed the same add-in to be used on Mac and Windows computers, and added features and improvements.

In Excel 2016, by the way, Microsoft has finally introduced their own Waterfall Chart as a built-in chart type, but mine is way better, with more options and features.

All this sounds great, but people want to actually try something before they spend their hard-earned cash. So the demo will have all of the features of the Advanced Edition of the software, and it will work for 15 days after installation. There will be a minimum of notices about the demo status of the software, because those suck.

Of course, making a demo is a huge undertaking. I have to make sure it shows off all the features of the software, but only for a limited time, so I can stay in business. So my demo is actually a beta test of a demo. I need some feedback from serious potential users so I can assess the effectiveness of the demo.

Here’s how it will work. The first 15 people who comment below with their email address will get a return email containing a link to the demo. The link will only work once for each user, so don’t go sharing it. I’ll remove your email address from your post as soon as I respond so you won’t risk getting spammed.

I would like these testers to install the demo, test it out, and give me feedback, both about the regular features and about the way the demo works. I’m interested in the answers to questions like these:

  • Does being a demo limit the features in ways I hadn’t noticed?
  • Does the demo expire when expected?
  • Are the notices helpful and not bothersome?

In exchange for this vital feedback, I will offer a $20 discount to these testers, so they will be able to purchase an Advanced license for the price of the Standard.

Edit 1

I’ve got the fifteen volunteers to test out the demo. After I get some feedback from these good folks, I’ll make the demo generally available.

Edit 2

I’ve gotten some great feedback, suggestions, and bug reports; most of this relates to the program itself, not the demo version. I’ve been working furiously to fix bugs and implement other enhancements. Stay tuned.

 

Peltier Tech Charts for Excel

Upcoming 2017 Conferences and Training

There are two exciting international conferences coming up in the next few months. I will be participating in 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

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)


 

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

Peltier Tech Charts for Excel 3.0