Automatically Open Excel and Send Report Using Batch and VBA
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 .
|
1 2 3 4 5 6 |
@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”
|
1 2 3 4 5 6 7 8 9 10 11 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
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.
|
1 2 3 4 5 6 7 8 9 10 |
Option Explicit Sub refreshData() ' ' refreshData Macro ' ' ActiveWorkbook.RefreshAll ActiveWorkbook.Save End Sub |
This sends email to recipients after workbook is saved
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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
|
1 2 3 |
Sub closeBook() Application.Quit End Sub |
Below is our sample generated table report which is being sent automatically using VBA code
