Excel Based Reporting

Hello Everyone, so today my focus is on to give insights about how can we deal with scenarios where the client wants us to help them with Excel based reporting.

We will be doing this using SSIS as we will need automation of the report on a daily basis.

Technology Items used -
1. SSIS
2. Excel files (format files)
3. Macro codes
4. Conditional formatting.
5. SSIS
6. C#.Net / VB.Net Script Components

Step-1: - We will be writing a few macro codes to do the same as we will need the numeric columns to be in the numeric while we insert the data from SSIS it will automatically convert it to Text.

Macro-1 - Code to Convert Text to Integer.

Sub Function_Name()
sheetlist = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7")
For i = LBound(sheetlist) To UBound(sheetlist)
Worksheets(sheetlist(i)).Activate
' Your Code Goes Here
Range("F:F").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "#,##0.00"
.Value = .Value
End With

Next
End Sub

Macro-2 - Code to Activate the first sheet in the excel work book.

Sub ActivateFirstWorksheet()
Dim ws As Worksheet
        
        For Each ws In ActiveWorkbook.Sheets
        ws.Activate
        ws.[a1].Select
        Next ws
        ActiveWorkbook.Worksheets(1).Activate

End Sub

Macro-3 - Code to Convert the Data Table in to Pivot Table.

Sub createPivotTableExistingSheet()

    'Source: https://powerspreadsheets.com/
    'For further information: https://powerspreadsheets.com/vba-create-pivot-table/

    'declare variables to hold row and column numbers that define source data cell range
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long

    'declare variables to hold source and destination cell range address
    Dim mySourceData As String
    Dim myDestinationRange As String

    'declare object variables to hold references to source and destination worksheets, and new Pivot Table
    Dim mySourceWorksheet As Worksheet
    Dim myDestinationWorksheet As Worksheet
    Dim myPivotTable As PivotTable

    'identify source and destination worksheets
    With ThisWorkbook
        Set mySourceWorksheet = .Worksheets("SheetName")
        Set myDestinationWorksheet = .Worksheets("PivotTable")
    End With 

    'obtain address of destination cell range
    myDestinationRange = myDestinationWorksheet.Range("A5").Address(ReferenceStyle:=xlR1C1)

    'identify row and column numbers that define source data cell range
    myFirstRow = 1
    myLastRow = 20005
    myFirstColumn = 1
    myLastColumn = 17

    'obtain address of source data cell range
    With mySourceWorksheet.Cells
        mySourceData = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn)).Address(ReferenceStyle:=xlR1C1)
    End With

    'create Pivot Table cache and create Pivot Table report based on that cache
    Set myPivotTable = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=mySourceWorksheet.Name & "!" & mySourceData).CreatePivotTable(TableDestination:=myDestinationWorksheet.Name & "!" & myDestinationRange, TableName:="PivotTableExistingSheet")

    'add, organize and format Pivot Table fields
    With myPivotTable
        .PivotFields("Instrument").Orientation = xlRowField
        .PivotFields("Trade Type").Orientation = xlColumnField
        With .PivotFields("Nominal")
            .Orientation = xlDataField
            .Position = 1
            .Function = xlSum
            .NumberFormat = "#,##0.00"
        End With
    End With


End Sub

Calling the Macros using the SSIS Script Task - 

Once this all is done We need to get these macros called from SSIS using the script task and then save the workbook as shown below -

#Region "Imports"

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.Office.Interop.Excel

#End Region

'ScriptMain is the entry point class of the script.  Do not change the name, attributes,

'or parent of this class.

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()>

<System.CLSCompliantAttribute(False)>

Partial Public Class ScriptMain

    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Public Sub Main()

        Dim excel As New Microsoft.Office.Interop.Excel.Application

        Dim wb As Microsoft.Office.Interop.Excel.Workbook

        wb = excel.Workbooks.Open("Macro Based Excel file Path.xlsm")

        excel.Run("Macro_1")

        excel.Run("Macro_2")

        excel.Run("Macro_3")

        excel.Run("Macro_4")

        excel.Run("Macro_5")

        'wb.RefreshAll()

        wb.Save()

        wb.Close()

        excel.Quit()

        Dts.TaskResult = ScriptResults.Success

    End Sub

#Region "ScriptResults declaration"

    'This enum provides a convenient shorthand within the scope of this class for setting the

    'result of the script.

    'This code was generated automatically.

    Enum ScriptResults

        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success

        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

    End Enum

#End Region

End Class

Hope you liked this Article, Happy Learning !