Excel VBA can be used to import a CSV file into a sheet very easily.  The Excel VBA code generated is mirrored in the Excel GUI by using the Data menu, Import External Data option, and the Import Data function.

Importing a CSV file directly into Excel using VBA is a great way to allow you to interface with external data systems easier, rather than doing a manual copy and paste.  Sometimes you just don't have the ability to do a web query on everything.

Here is the sample code:

' filename = CSV filename without directory (test.csv)
' outSheet = name of the worksheet in the current workbook
'            where the data should go, will start in A1
Function doFileQuery(filename As String, outSheet As String) As Boolean
    Dim rootDir As String
    rootDir = "C:\myDirectory"
    Dim connectionName As String
    connectionName = "TEXT;" + rootDir + "\" + filename
    With Worksheets(outSheet).QueryTables.Add(Connection:=connectionName, Destination:=Worksheets(outSheet).Range("A1"))
        .Name = filename
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With
End Function

If you have a file other than a CSV file, just change the Excel VBA options (.TextFileTabDelimiter, .TextFileSemicolonDelimiter, etc..) to True and the (.TextFileCommaDelimiter) to False.

Sometimes, its easier to embed some formulas in an Excel spreadsheet than to automate it completely by VB, so I will show you how to fill down formulas using Excel VBA.

Here is the code to fill down a formula.  The arguments you pass to the function is the sheet name, the start column and end column of your columns that have functions in them that you would like to fill down.

Function fillDownFormulas(sheetName As String, startC As String, endC As String) As Boolean
  Dim WSD As Worksheet
  Set WSD = Worksheets(sheetName)
  ' Find the last row with data
  Dim finalRow As Long
  finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
  If finalRow <= 2 Then Exit Function
  ' Fill formulas down
  Dim sourceRange As Range
  Set sourceRange = WSD.Range(WSD.Cells(2, startC), WSD.Cells(2, endC))
  Dim fillRange As Range
  Set fillRange = WSD.Range(WSD.Cells(2, startC), WSD.Cells(finalRow, endC))
  sourceRange.AutoFill Destination:=fillRange, Type:=xlFillDefault
End Function

You can call the function like this:

fillDownFormulas("sheet1","G","I")
fillDownFormulas("sheet2","D","D")

I have been working on designing some code for excel VBA chart automation to generate an excel chart for every line in an excel spredsheet.  Every row in the excel spreadsheet has the information required to do the chart.  I am trying to generate a chart for every row in the spreadsheet to include in a mail merge between Word and Excel, where I want a different chart in every mail merged document.

So first things first, let me show you how to create a chart using Excel VBA.

There is a lot of code, so I will break down the Excel VBA chart creation code into smaller parts so that I can explain them, and then I will put it together in the end for you.

The first step is to see what my data looks like, here is a screenshot sample of the excel spreadsheet.

So using this example, I want to generate 5 different bar charts, with dollars on the y axis, the categories Employee Cost and Company Cost on the x axis, and the data will create the bars in the chart.  And I'm also picky about formatting, so I want to make things look nice too, so you will see how I automate the formatting of the chart also.

Function CreateBarCharts() As Boolean
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    
    Dim sheetName As String
    sheetName = "DataSource"
    Dim WSD As Worksheet
    Set WSD = Worksheets(sheetName)
    
    Dim chartSheet As String
    chartSheet = "ChartOutput"
    Dim CSD As Worksheet
    Set CSD = Worksheets(chartSheet)

As you can see for an added degree of difficulty, I will be putting the charts that I am creating on a different sheet than my source data.

    ' get the current charts so proper overwriting can happen
    Dim chtObjs As ChartObjects
    Set chtObjs = CSD.ChartObjects

    ' Turn off autofilter mode
    WSD.AutoFilterMode = False
    
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row

To determine how many values to loop over, I will find the last row in the data set. Here, that value would be 5.

    ' for each row in the sheet
    For i = 2 To finalRow
    
        Dim chartName As String
        chartName = WSD.Cells(i, 5).Value
    
        ' Delete chart if it already exists, we are making a new one
        Dim chtObj As ChartObject
        For Each chtObj In chtObjs
            If chtObj.Name = chartName Then
                chtObj.Delete
            End If
        Next
    
        ' define chart data range for the row (record)
        Dim dataString As String
        dataString = "C" & i & ":D" & i
        Set rngChtData = WSD.Range(dataString)
        
        ' define the x axis values
        Set rngChtXVal = WSD.Range("$C$1:$D$1")

        ' add the chart
        Charts.Add
        With ActiveChart
            
            ' make a bar chart
            .ChartType = xlColumnClustered

            ' remove extra series
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
            

            ' add series from selected range, column by column

            With .SeriesCollection.NewSeries
                .Values = rngChtData
                .XValues = rngChtXVal
                .Name = "Cost"
            End With
            
            .Location Where:=xlLocationAsObject, Name:=chartSheet

        End With

Here I go through every row in the data, I use a cell value to determine the name of the chart, which is something very important for a mail merge. That is the purpose of the chartName variable. I use it to delete any existing charts, and to name the chart to make it callable from the mail merge, which I will discuss in another article.

So my data lies in columns C and D, so you see me define those data ranges, and also the x axis categories that are in C1 and D1. I then add the chart, set a couple options, and insert it into the spreadsheet. But I'm not done yet, due to some weirdness in the ordering of the commands, here is another With ActiveChart block to include inside the for loop to finish out the chart creation.

      With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Benefits Cost"
            .Parent.Name = WSD.Cells(i, 5).Value
            .Legend.Delete
        
            .Axes(xlCategory).TickLabels.AutoScaleFont = False
            With .Axes(xlCategory).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .ChartTitle.AutoScaleFont = False
            With .ChartTitle.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
     
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
        
        End With

All of this code will set the name of the chart, delete the legend, and do a bunch of formatting. But we're not done yet. I wanted to control the width and height of the chart, so I have another block to add in the For loop.

        ' Set the height and width
        With CSD.ChartObjects(chartName)
            .Width = 225
            .Height = 175
        End With

This was another case where the ordering of calling the different commands made a difference, so I separated it out at the end of the excel VBA chart creation.

So here is the finished entire block of code:

Function CreateBarCharts() As Boolean
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long
    
    Dim sheetName As String
    sheetName = "DataSource"
    Dim WSD As Worksheet
    Set WSD = Worksheets(sheetName)
    
    Dim chartSheet As String
    chartSheet = "ChartOutput"
    Dim CSD As Worksheet
    Set CSD = Worksheets(chartSheet)
    
    ' get the current charts so proper overwriting can happen
    Dim chtObjs As ChartObjects
    Set chtObjs = CSD.ChartObjects

    ' Turn off autofilter mode
    WSD.AutoFilterMode = False
    
    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    Dim i As Integer
    
    ' for each row in the sheet
    For i = 2 To finalRow
    
        Dim chartName As String
        chartName = WSD.Cells(i, 5).Value
    
        ' Delete chart if it already exists, we are making a new one
        Dim chtObj As ChartObject
        For Each chtObj In chtObjs
            If chtObj.Name = chartName Then
                chtObj.Delete
            End If
        Next
    
        ' define chart data range for the row (record)
        Dim dataString As String
        dataString = "C" & i & ":D" & i
        Set rngChtData = WSD.Range(dataString)
        
        ' define the x values
        Set rngChtXVal = WSD.Range("$C$1:$D$1")

        ' add the chart
        Charts.Add
        With ActiveChart
            
            ' make a bar chart
            .ChartType = xlColumnClustered

            ' remove extra series
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop
            

            ' add series from selected range, column by column

            With .SeriesCollection.NewSeries
                .Values = rngChtData
                .XValues = rngChtXVal
                .Name = "Cost"
            End With
            
            .Location Where:=xlLocationAsObject, Name:=chartSheet

        End With

        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Benefits Cost"
            .Parent.Name = WSD.Cells(i, 5).Value
            .Legend.Delete
        
            .Axes(xlCategory).TickLabels.AutoScaleFont = False
            With .Axes(xlCategory).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .Axes(xlValue).TickLabels.AutoScaleFont = False
            With .Axes(xlValue).TickLabels.Font
                .Name = "Arial"
                .FontStyle = "Regular"
                .Size = 8
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
            
            .ChartTitle.AutoScaleFont = False
            With .ChartTitle.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 12
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = xlUnderlineStyleNone
                .ColorIndex = xlAutomatic
                .Background = xlAutomatic
            End With
     
            With .PlotArea.Interior
                .ColorIndex = 2
                .PatternColorIndex = 1
                .Pattern = xlSolid
            End With
        
        End With
        
        ' Set the height and width
        With CSD.ChartObjects(chartName)
            .Width = 225
            .Height = 175
        End With
    Next i
End Function

To help automate data processing using Excel VBA, one of the most useful tools to use is a PivotTable.  But you may be thinking that a PivotTable is so complicated to do by hand, that it must be very hard to do in VBA.  I'm here to show you that it really isn't that hard.  I often will use a PivotTable created using VBA to create a view on the data that allows me to use GetPivotData() calls from within the excel spreadsheet to pull summaries out of the PivotTable like I need.

Here is the data that is located on Sheet 1 that I will make the pivot table out of.

To be able to see the summary of hours by the different activities, I would solve this problem by creating a pivot table in excel that would have a row containing Activity, and the data would be a sum of Hours.

To do this in VB, I will take the data on Sheet1, and create a pivot table on Sheet2, using this code.

Sub MakePivotTable()
    Dim pt As PivotTable
    Dim strField As String
    Dim WSD As Worksheet
    Set WSD = Worksheets("Sheet1")
    Dim PTOutput As Worksheet
    Set PTOutput = Worksheets("Sheet2")
    Dim PTCache As PivotCache
    Dim PRange As Range

    ' Find the last row with data
    Dim finalRow As Long
    finalRow = WSD.Cells(Application.Rows.Count, 1).End(xlUp).Row
    
    ' Find the last column with data
    Dim finalCol As Long
    finalCol = WSD.Cells(1, Application.Columns.Count).End(xlToLeft).Column
    
    ' Find the range of the data
    Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=PRange)

    ' Create the pivot table
    Set pt = PTCache.CreatePivotTable(TableDestination:=PTOutput.Cells(1, 1), _
    TableName:="SamplePivot")
    
    ' Define the layout of the pivot table
    
    ' Set update to manual to avoid recomputation while laying out
    pt.ManualUpdate = True
    
    ' Set up the row fields
    pt.AddFields RowFields:=Array( _
       "Activity")

    ' Set up the data fields
    With pt.PivotFields("Hours")
        .Orientation = xlDataField
        .Function = xlSum
        .Position = 1
    End With
    
  ' Now calc the pivot table
    pt.ManualUpdate = False
    
End Sub

The PivotTable that this creates is: