We can use Visual Basic codes to automate some reports in Excel. Below are some simple codes to do this.

First this is a batch file to open our Excel report called “data report.xlsm”, assuming the report file is in the same folder as the batch file. You can schedule the time this batch file is called using Windows Scheduler .

@echo off
set excel_program=C:\Program Files\Microsoft Office\OFFICE11\excel.exe
set xls_files=C:\Documents and Settings\Paul\My Documents
:
cd "%xls_files%"
start "%excel_program%" "data report.xlsm"

 

This calls some functions if the opened ActiveWorkBook is called “data report” and the extension is “xlsm”

Private Sub Workbook_Open()
If ActiveWorkbook.Name = "data report.xlsm" Then
Call insertCityData2
Call refreshData
Call Mail_workbook_Outlook_1
Call closeBook


End If

End Sub

 

This clears the original data in the report and inserts a fresh new table data into a Sheet named “cityData” and then saves the entire workbook afterwards. The Excel sheet is already connected to Sql Server database which populates the table with information

Sub insertCityData2()
'
' insertCityData2 Macro
'

'
Sheets("cityData").Visible = True

'select the sheet to paste into
Sheets("cityData").Select
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Antrak;Data Source=.\MSSQLSERVER_ENT;Use" _
, _
" Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LAPTOP-53L11MB9;Use Encryption for Data=False;Tag w" _
, "ith column collation when possible=False"), Destination:=Range("$A$1")). _
QueryTable
.CommandType = xlCmdSql
.CommandText = Array( _
"/*lets get some statistic*/" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "select" & Chr(13) & "" & Chr(10) & " City" & Chr(13) & "" & Chr(10) & " ,sum([Total Cost]) as [£ Gross Revenue]" & Chr(13) & "" & Chr(10) & " ,avg([Total Cost]) as [" _
, _
"£ Avg Revenue]" & Chr(13) & "" & Chr(10) & " ,sum([Insurance]) as [Gross insurance]" & Chr(13) & "" & Chr(10) & " ,sum([AFP]) as [Gross Afp]" & Chr(13) & "" & Chr(10) & " ,avg(age) as [A" _
, _
"vg Age]" & Chr(13) & "" & Chr(10) & " ,avg([number of passengers]) as [Avg Pax]" & Chr(13) & "" & Chr(10) & " " & Chr(13) & "" & Chr(10) & " " & Chr(13) & "" & Chr(10) & "from " & Chr(13) & "" & Chr(10) & " [Antrak].[dbo].[FlightBookingsMain]" & Chr(13) & "" & Chr(10) & "grou" _
, "p by" & Chr(13) & "" & Chr(10) & " City" & Chr(13) & "" & Chr(10) & "order by " & Chr(13) & "" & Chr(10) & " [£ Gross Revenue] desc")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_ExternalData_13"
.Refresh BackgroundQuery:=False
End With
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
ActiveWorkbook.Save
End Sub

This refreshes all sheets in the WorkBook.

Option Explicit
Sub refreshData()
'
' refreshData Macro
'

'
ActiveWorkbook.RefreshAll
ActiveWorkbook.Save
End Sub

 

This sends email to recipients after workbook is saved

Sub Mail_workbook_Outlook_1()
'Working in Excel 2000-2016
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.to = "recipient1@gmail.com; recipient2@yahoo.co.uk; recipient3@hotmail.co.uk"
.CC = ""
.BCC = ""
.Subject = "Report Sent From Outlook"
.Body = "Hi, Please find attached, the report for the week"
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

This closes the entire Excel after we have emailed the workbook

Sub closeBook()
Application.Quit
End Sub

Below is our sample generated table report which is being sent automatically using VBA code