' This macro reads in all CSV files in a directory and
' writes them to a single new worksheet ("Sheet1")
' in a new workbook (that is, the workbook in which this
' Macro is defined).
' The data from the separate CSV files are column-listed in' the Sheet1.
' Found at this web site: https://www.extendoffice.com/documents/excel/3388-excel-import-multiple-text-csv-xml-files.html
' Developed in this Macro 2018-01-08
' John Zaleski
' What follows is directly from the web site:
' UpdatebyKutoolsforExcel 2015-12-14
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
If xStrPath = "" Then Exit Sub
Set xSht = ThisWorkbook.ActiveSheet
If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
Columns(1).SpecialCells(xlBlanks).Value = _
ActiveSheet.UsedRange.Copy xSht.Range("A" _
xFile = Dir
Application.ScreenUpdating = True
MsgBox "no files csv", , "Kutools for Excel"
I am frequently in need of generating scatter and line plots of measured physiological signals. To this end, I like to use many different types of software tools. The one I like the most, however, for rapid manipulation and visualization of data is Microsoft Excel.
Oftentimes I need to create charts from columnar data within Excel. Creating and formatting line plots, particularly if creating many, can be a time-consuming and tedious process. Therefore, I had decided on searching for approaches for creating automated chart plotting routines of the format I was looking for. Naturally, I turned to the Internet and to my various MS Excel textbooks.
To my disappointment, however, after spending what was perhaps a week of evenings searching, I honestly could not find anything that fit the bill on the open Internet. That is to say, it was not as if I could not find any help… simply that I could not find anything that already provided me with exactly what I was looking for or event a close template that I could pilfer and customize.
Thus, it became necessary for me to slog through the process myself. The purpose of this entry is to simply communicate what I found and did so that another wayward traveler might be saved from some time and effort… although, I can imagine that what I provide below is not precisely what someone else is looking for, either.
Nevertheless, and once you review my code, you may conclude “that really was not anything special… why did it take him so long to create that, and why so difficult to find comparable models that he could reuse?”
The answer to these questions is that oftentimes those pursuing the process need to educate themselves. Thus, it was not merely me taking another individual’s sample code…it was about me understanding that sample code. The understanding is often the hardest part of the challenge.
The objective is to create and X-Y plot, wherein the X-data and Y-data are columns in an MS Excel Worksheet, as shown in the figure below:
These data can be plotted either one dependent variable at a time or any combination of dependent columns versus the independent column. Accomplishing this manually using MS Excel is a straightforward task. Yet, if one wishes to develop a standardized template or create a common format, particularly if the plotting is to be repeated many times, performing this manually becomes overwhelmingly tedious.
The routine I developed creates a chart with multiple data series displayed versus the common independent axis. The plot generated by the Visual Basic code from these data is as shown below:
The code follows:
Sub plotData()' Purpose: Plots 3 functions versus time in Excel using
' visual basic programming.' 2017-12-31.' J. Zaleski' how many rows? Dim rCount As Long rCount = ActiveSheet.Cells(9, 10)' axis dimensions Dim xaxis As Range Dim yaxis As Range Dim yaxis2 As Range Dim yaxis3 As Range Set xaxis = Range("$a$3", "$a" & rCount) Set yaxis = Range("$b$3", "$b" & rCount) Set yaxis2 = Range("$c$3", "$c" & rCount) Set yaxis3 = Range("$d$3", "$d" & rCount) ' dimension chart Dim c As Chart Set c = ActiveWorkbook.Charts.Add Set c = c.Location(Where:=xlLocationAsObject, Name:="test")With c .ChartType = xlXYScatterLines 'A scatter plot, not a line chart! ' set other chart properties End With' add data series to chartWith c ' assign x and y value ranges to series 1.SeriesCollection.NewSeries .SeriesCollection(1).Name = Worksheets("test").Cells(2, 2) .SeriesCollection(1).Values = yaxis .SeriesCollection(1).XValues = xaxis .SeriesCollection(1).MarkerBackgroundColor = RGB(0, 0, 0) .SeriesCollection(1).MarkerForegroundColor = RGB(0, 0, 0) .SeriesCollection(1).MarkerSize = 2 .SeriesCollection(1).MarkerStyle = 3 .SeriesCollection(1).Format.Line.Weight = 1# .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0) .SeriesCollection(1).Format.Line.BackColor.RGB = RGB(0, 0, 0)' assign x and y value ranges to series 2.SeriesCollection.NewSeries .SeriesCollection(2).Name = Worksheets("test").Cells(2, 3) .SeriesCollection(2).Values = yaxis2 .SeriesCollection(2).XValues = xaxis .SeriesCollection(2).MarkerBackgroundColor = RGB(128, 0, 0) .SeriesCollection(2).MarkerForegroundColor = RGB(128, 0, 0) .SeriesCollection(2).MarkerSize = 2 .SeriesCollection(2).MarkerStyle = 4 .SeriesCollection(2).Format.Line.Weight = 1# .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(128, 0, 0) .SeriesCollection(2).Format.Line.BackColor.RGB = RGB(128, 0, 0)' assign x and y value ranges to series 3.SeriesCollection.NewSeries .SeriesCollection(3).Name = Worksheets("test").Cells(2, 4) .SeriesCollection(3).Values = yaxis3 .SeriesCollection(3).XValues = xaxis .SeriesCollection(3).MarkerBackgroundColor = RGB(128, 128, 0) .SeriesCollection(3).MarkerForegroundColor = RGB(128, 128, 0) .SeriesCollection(3).MarkerStyle = 5 .SeriesCollection(3).MarkerSize = 2 .SeriesCollection(3).Format.Line.Weight = 1# .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(128, 128, 0) .SeriesCollection(3).Format.Line.BackColor.RGB = RGB(128, 128, 0)End With'adjust major unit on x axisWith c.Axes(xlCategory) .MajorUnit = Worksheets("test").Cells(8, 10) End With'adjust major unit on y axisWith c.Axes(xlValue) .MajorUnit = Worksheets("test").Cells(5, 10) End With' find maximum y valueDim maxY As Integer maxY = Worksheets("test").Cells(3, 10)' find minimum y valueDim minY As Integer minY = Worksheets("test").Cells(4, 10)' find maximum x valueDim maxX As Integer maxX = Worksheets("test").Cells(6, 10)' find minimum x valueDim minX As Integer minX = Worksheets("test").Cells(7, 10)With c 'locate chart.ChartArea.Top = 50 .ChartArea.Left = 400'adjust width and height.ChartArea.Width = 400 .ChartArea.Height = 200'adjust y-axis Scale.Axes(xlValue).MinimumScale = minY .Axes(xlValue).MaximumScale = maxY'adjust x-axis Scale.Axes(xlCategory).MinimumScale = minX .Axes(xlCategory).MaximumScale = maxX'adjust font.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8 .ChartArea.Format.TextFrame2.TextRange.Font.Name = "Arial Narrow"'adjust font color.Axes(xlCategory).TickLabels.Font.Color = RGB(0, 0, 0) .Axes(xlValue).TickLabels.Font.Color = RGB(0, 0, 0)'adjust decimal places on x-axis.Axes(xlCategory).TickLabels.NumberFormat = "0.00" .Axes(xlValue).TickLabels.NumberFormat = "0.00"'adjust plot labels.Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Worksheets("test").Cells(2, 1) .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Three Functions versus Time" .HasLegend = True End WithEnd Sub
The code is written as a macro and the data are maintained in a worksheet called “test”. The macro is associated with a button placed on the worksheet which, when pressed, creates a plot of the three dependent variables. The routine is not intended to be all-encompassing in terms of capability. But, to the uninitiated, the code serves as a building block for tailoring and customization. For instance, the routine requires three specific dependent columns of data. The .SeriesCollection field is specified with three indices. This can be generalized to any number of plots using a for loop indexing variable. Furthermore, the color schemes, tick labels, size, location, etc. are all customizable.