Building BI Solutions with Excel VBA: Unleashing the Power of Data

  Sub DataIntegrationWithPowerQueryAndPivot()
    ' Display a message indicating the data integration process
    MsgBox "Integrating data with Power Query and Power Pivot using Excel VBA."

    ' Call the function to perform data integration
    PerformDataIntegration
End Sub

Sub PerformDataIntegration()
    ' Declare variables
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range
    Dim connectionName As String

    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Assuming data is in column A starting from row 1
    Set rng = ws.Range("A1").CurrentRegion

    ' Convert the data into an Excel Table (ListObject)
    Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "DataTable"

    ' Get the table's QueryTable
    Set qryTable = tbl.QueryTable

    ' Define Power Query M formula for data transformation
    qryTable.CommandText = "let" & vbCrLf & _
                            "    Source = Excel.CurrentWorkbook(){[Name=""DataTable""]}[Content]," & vbCrLf & _
                            "    #" & "Changed Type" = Table.TransformColumnTypes(Source,{{""Column1"", type text}})" & vbCrLf & _
                            "in" & vbCrLf & _
                            "    #" & "Changed Type"

    ' Refresh the query table to apply transformations
    qryTable.Refresh

    ' Create a connection for Power Pivot
    connectionName = "DataTableConnection"
    ThisWorkbook.Connections.Add2 Name:=connectionName, Description:="Connection for Power Pivot", _
                                ConnectionString:=qryTable.Connection, _
                                CommandText:=qryTable.CommandText, _
                                lCmdtype:=xlCmdSql

    ' Refresh the Power Pivot data model
    ThisWorkbook.RefreshAll

    ' Display a message indicating the successful data integration
    MsgBox "Data integration with Power Query and Power Pivot completed. Ready for advanced analytics!"
End Sub
  

  Option Explicit

Sub AutomatedReportingWithEmailAlerts()
    ' Display a message indicating the automated reporting setup
    MsgBox "Automating reporting with email alerts in Excel VBA."

    ' Call the function to perform automated reporting
    PerformAutomatedReporting
End Sub

Sub PerformAutomatedReporting()
    ' Declare variables
    Dim ws As Worksheet
    Dim reportRange As Range
    Dim outlookApp As Object
    Dim outlookMail As Object
    Dim emailRecipient As String
    Dim emailSubject As String
    Dim emailBody As String

    ' Set the active worksheet
    Set ws = ActiveSheet

    ' Assuming data is in column A starting from row 1
    Set reportRange = ws.Range("A1").CurrentRegion

    ' Create Outlook objects
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)

    ' Define email details
    emailRecipient = "recipient@example.com"
    emailSubject = "Automated Report"
    emailBody = "Dear User," & vbCrLf & _
                "Please find the attached automated report." & vbCrLf & _
                "Regards," & vbCrLf & _
                "Your Name"

    ' Attach the report to the email
    reportRange.Copy
    outlookMail.Attachments.Add Environ("temp") & "\AutomatedReport.xlsx"

    ' Compose the email
    With outlookMail
        .To = emailRecipient
        .Subject = emailSubject
        .Body = emailBody
        .Display ' Change to .Send to automatically send the email
    End With

    ' Display a message indicating the successful automated reporting
    MsgBox "Automated reporting with email alerts completed. Report sent!"
End Sub