ExcelSujeet (YouTube Blogger)

Power Automate - Automatically fetch data from Power BI in to Excel and Send the copy of the Excel file via Email Step-1 Step-2 Step-3 Using Command text we can make changes in the data we fetch from the Power BI. Like add fields/columns, filter data, rename columns. It is a DAX formula. Step-4 Step-5      Step-6 Step-7 Posting message on Teams. We can post message to a Teams Group  also by selecting Group chat from Post in and select the Teams Group from the Group chat  dropdown that will appear after making selection. Step-8 Location:  We need to select the Group  on SharePoint in which our file is located. Document Library: We need to select the Documents option from the dropdown. File: Select the file on which you want to run the script. Script: Select the script to perform the desired action. Step-9 Step-10 Step-11 Using Filter Query  we can decide which file properties we want to get. We are performing this step to attach excel as an attach...

Power Query & Power BI https://learnpowertoolwithexcelsujeet.blogspot.com/ Power Query & Power BI https://learngaswithexcelsujeet.blogspot.com/

Copy Headings from one table into other table: create table test2 like table1; Insert data from one table into other table: insert into table2 select * from table1 Find duplicate records in a table: SELECT * ,           ROW_NUMBER () OVER (               PARTITION BY emp_id, name, age, doj, dob  ) AS row_num FROM EmployeeTable; Note: Write all columns name in the table. This new row_num is not part of the table. Delete duplicate records in a table: To delete duplicate records we can use a CTE (Common Table Expression) with the ROW_NUMBER() function. Alter table layoffs_staging_2 add row_num int; ----------------------------------------------------------------------------------------------------------------------------- With CTE as ( SELECT *,        ROW_NUMBER() OVER(            PARTITION BY company, location, industry, total_laid_off, percentage_laid_off, 'date',...

 Use the below line right after copying & pasting data using VBA. Below line clears the copied data from the clipboard. Application.CutCopyPaste=False Without that line you will get the warning 'There is a large amount of information on the Clipboard....' when you close the workbook with a large amount of data on the clipboard.

Early Binding   Dim oApp as Outlook.Application Dim oEmail as Outlook.MailItem Dim oNS as Outlook.NameSpace Set oApp=New Outlook.Application Set oEmail=oApp.CreateItem(olMailItem) Set oNS=oApp.GetNamespace("MAPI")  'Messaging Application Programming Interface oEmail.SendUsingAccout=oNS.Accounts.Item(1) 'To send email from a specific account. Or Use With oEmail     .SentOnBehalfOfName="Enter the specific account email-id." end With Late Binding Dim oApp as Object Dim oEmail as Object Dim oNS as Object Set oApp=CreateObject("Outlook.Application") Set oEmail=oApp.CreateItem(0) Set oNS=oApp.GetNamespace("MAPI") 'Messaging Application Programming Interface oEmail.SendUsingAccount=oNS.Accounts.Item(1) 'To send email from a specific account. Or Use With oEmail     .SentOnBehalfOfName="Enter the specific account email-id." end With

Early Binding First establish the reference with the outlook library by going to 'Tools' Tab then Click on 'References', from the available reference Select the "Microsoft Outlook 16.0 Object Library", 16.0 could be 14.0 or 15.0 basis on the version of the Microsoft Office in your system. Dim oApp as Outlook.Application Dim oEmail as Outlook.MailItem Dim oNS as Outlook.NameSpace Set oApp=New Outlook.Application Set oEmail=oApp.CreateItem(olMailItem) Set oNS=oApp.GetNamespace("MAPI")  oEmail.SendUsingAccout=oNS.Accounts.Item(1) 'To send email from a specific account. Late Binding No need to establish relationship with any library. Dim oApp as Object Dim oEmail as Object Dim oNS as Object Set oApp=CreateObject("Outlook.Application") Set oEmail=oApp.CreateItem(0) Set oNS=oApp.GetNamespace("MAPI") oEmail.SendUsingAccount=oNS.Accounts.Item(1) 'To send email from a specific account.

 Outlook Email Automation Examples 1) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Sub SendMail_Bangalore() Dim oApp As Outlook.Application Dim OEmail As Outlook.MailItem Dim signature As String Dim str As String Dim loc As String Set oApp = New Outlook.Application Set OEmail = oApp.CreateItem(olMailItem) Dim olNS As Outlook.Namespace Set olNS = Outlook.Application.GetNamespace("MAPI") OEmail.SendUsingAccount = olNS.Accounts.Item(1) loc = "Bangalore" If Sheet1.Range("b10").Value < 2 Then str = "Hi All," & "<br/><br/>" & "Mentioned Package ID " & Sheet1.Range("b9").Value & " has been renewed for next " & Sheet1.Range("b10").Value _ & " day. Service will start from " & Format(Sheet1.Range("b11").Value, "DD-MMM") & "." & "<br/><br/>" Else str = "Hi All...

Use the below function in your subroutine in the below mentioned way: .HTMLBody = str & rngHTML(Sheet1.Range("a13:g17"))  & .HTMLBody ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Function rngHTML(rng As Range)     Dim fso As Object, ts As Object, TempWB As Workbook     Dim TempFile As String     TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"     '' copy the range and create a new workbook to paste the data into     rng.Copy     Set TempWB = Workbooks.Add(1)     With TempWB.Sheets(1)         .Cells(1).PasteSpecial Paste:=8         .Cells(1).PasteSpecial xlPasteValues, , False, False         .Cells(1).PasteSpecial xlPasteFormats, , False, False         .Cells(1).Select         Application.CutCopyMode = False         O...

Outlook Email Automation Using VBA   Hyperlink in Mail Body strMailBody="Please click on the following link " & "<a href=""https://www.google.com/"">www.google.com</a>" Note: Don't forget to include https here or copy the URL from the browser address bar. Choose Email Account .SentOnBehalfOfName=str_OnBehalfOf Here string variable str_OnBehalfOf contains the email address of the account using which emails will be sent.

Control Pivot Table Slicers Using VBA Paste below code in the Worksheet Code Module Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'Variables to store drop down values Dim str_Location As String, str_Region As String Dim sli As SlicerItem If Application.Intersect(Target, Range("B1:C2")) Is Nothing Then Exit Sub If Sheet1.Range("B1").Value <> "Location" Then     Sheet1.Range("B1").Value = "Location" End If If Sheet1.Range("C1").Value <> "Region" Then     Sheet1.Range("C1").Value = "Region" End If str_Location = Sheet1.Range("B2").Value str_Region = Sheet1.Range("C2").Value If str_Location = "All" Then     Sheet2.PivotTables("ThisPivotTable1").Slicers("Location").SlicerCache.ClearAllFilters End If If str_Region = "All" Then     Sheet2.PivotTables("ThisPivotTable1").Slicers("Region").S...

Control Pivot Table Filters Using VBA Paste below code in the Worksheet Code Module Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim str_Location As String, str_Region As String If Application.Intersect(Target, Range("B1:C2")) Is Nothing Then Exit Sub 'MsgBox "Yes, it intersect." If Sheet1.Range("B1").Value <> "Location" Then     Sheet1.Range("B1").Value = "Location" End If If Sheet1.Range("C1").Value <> "Region" Then     Sheet1.Range("C1").Value = "Region" End If str_Location = Sheet1.Range("B2").Value str_Region = Sheet1.Range("C2").Value If str_Location <> "All" Then     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").ClearAllFilters     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").CurrentPage = str_Location End If If str_Region <> ...

 Paste Excel Range to Outlook Email Body Using Chart Object Option Explicit Sub PasteExcelRangeIntoChart() Dim ch As Chart Dim rng As Range For Each ch In ThisWorkbook.Charts     ch.Delete Next ch Set ch = Charts.Add Set ch = ch.Location(xlLocationAsObject, "Sheet1") ch.Parent.Name = "ABC" With Sheet1.Range("F3")     ch.Parent.Left = .Left     ch.Parent.Top = .Top End With With Sheet1.Range("B3:C9")     ch.Parent.Width = .Width     ch.Parent.Height = .Height End With Set rng = Sheet1.Range("B3:C9") rng.CopyPicture xlScreen, xlBitmap ch.Paste ch.Export ThisWorkbook.Path & "\ABC.png" Call SendEmail End Sub Sub SendEmail() Dim oApp As Object Dim oMail As Object Dim imgPath As String Set oApp = CreateObject("Outlook.Application") Set oMail = oApp.createitem(0) imgPath = ThisWorkbook.Path & "\ABC.png" With oMail     .display     .htmlbody = "<img src=""" & imgPath & "...

Array Formula - Find Date & Time Just Greater than a Date & Time Array Formula =SMALL(($B$3:$B$10=$F$3)*($C$3:$C$10>$G$3)*($C$3:$C$10),SUM(IF(($B$3:$B$10=$F$3)*($C$3:$C$10>$G$3)*($C$3:$C$10)=0,1,0))+1) Paste the formula in a cell and press Ctrl + Enter on the keyboard to make it an array formula.

 Control Pivot Table's Page Filters Using VBA Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim str_Location As String, str_Region As String If Application.Intersect(Target, Range("B1:C2")) Is Nothing Then Exit Sub 'MsgBox "Yes, it intersect." If Sheet1.Range("B1").Value <> "Location" Then     Sheet1.Range("B1").Value = "Location" End If If Sheet1.Range("C1").Value <> "Region" Then     Sheet1.Range("C1").Value = "Region" End If str_Location = Sheet1.Range("B2").Value str_Region = Sheet1.Range("C2").Value If str_Location <> "All" Or str_Region <> "All" Then     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").ClearAllFilters     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").CurrentPage = str_Location          Sheet2.PivotTables(...

Excel Array Formulas Example-1 a) Formula in Col E (Press Ctrl + Shift + Enter to make it an Array Formula)     =IFERROR(INDEX($B$3:$B$13,SMALL(IF(ISNUMBER(SEARCH($E$2,$B$3:$B$13)),ROW($B$3:$B$13)-2),ROWS($D$3:$D3)),1),"") b) Formula in Col G (Press Ctrl + Shift + Enter to make it an Array Formula) =IFERROR(INDEX($B$3:$B$13,MATCH(0,COUNTIF($G$2:$G2,$B$3:$B$13),0),1),"") c) Ignore blanks - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($D$2:$D2,$B$3:$B$12)+IF($B$3:$B$12="",1,0),0),1),"") d) Only text - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($F$2:$F2,$B$3:$B$12)+IF(ISTEXT($B$3:$B$12),0,1),0),1),"") e) Only numbers - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($H$2:$H2,$B$3:$B$12)+IF($B$3:$B$12="",1,0)+IF(IS...

 1) Google Data Studio: Refreshing data of uploaded CSV file Ans:  a) If you are uploading the CSV from your local computer, Data Studio will not get updated with any changes you made later in the CSV. b) You can use Google Sheets to store your data, instead of Upload a CSV file as the data source, you can import the data from Google Sheets to Data Studio. c) Any updates on the Google Sheets will be refreshed on Data Studio after you click the Data Refresh button or wait for the default data updates which are 15 minutes.

 function GetAllSheetsName_1() {   const ss=SpreadsheetApp.getActiveSpreadsheet();   const shts=ss.getSheets();   shts.forEach((sht,index)=>{         //index is optional here     Logger.log(sht.getName() + " " + index)   }) } function GetAllSheetsName_2() {   const ss=SpreadsheetApp.getActiveSpreadsheet();   const shts=ss.getSheets();   Logger.log(shts.length)      for (let i=0;i<shts.length;i++){     Logger.log(shts[i].getName())   } } function GetDataRng(){   const ss=SpreadsheetApp.getActiveSpreadsheet();   const sht=ss.getSheetByName('Sheet4');   const dRng=sht.getDataRange();   Logger.log(dRng.getA1Notation()); } function GetRangeValues(){   const ss=SpreadsheetApp.getActiveSpreadsheet();   const sht=ss.getSheetByName('Sheet1');   //const data=sht.getDataRange(); //works like vba sheet1.usedrange.address   //sheet1.usedrange.address ...

 Auto Create Pivot Table Option Explicit Sub CreatePivotCache_N_PivotTable() ThisWorkbook.Activate Dim pc As PivotCache Dim pt As PivotTable Set pc = ThisWorkbook.PivotCaches.Create( _ SourceType:=xlDatabase, _ SourceData:=Sheet1.Range("A1").CurrentRegion, _ Version:=8) Set pt = pc.CreatePivotTable(TableDestination:=Sheet4.Range("A5"), _ TableName:="MyFirstPivotTable") 'Add Pivot Fields Sheet4.PivotTables("MyFirstPivotTable").PivotFields("State").Orientation = xlRowField Sheet4.PivotTables("MyFirstPivotTable").PivotFields("Region").Orientation = xlRowField Sheet4.PivotTables("MyFirstPivotTable").PivotFields("Region").Position = 1 Sheet4.PivotTables("MyFirstPivotTable").PivotFields("InsuredValue").Orientation = xlDataField Sheet4.PivotTables("MyFirstPivotTable").PivotFields("Sum of InsuredValue").Function = xlSum Sheet4.PivotTables("MyFirstPivotTable...

Handle Worksheet Change Event-Based Error   Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo err 'If Not Target.Cells.Address = "$C$8" Then Exit Sub If Target.Cells.Address = "$C$8" And Target.Value = "All" Then     Target.Offset(0, 1) = "All"     Target.Offset(0, 2) = "All"     Target.Offset(0, 3) = "All" ElseIf Target.Cells.Address = "$D$8" And Target.Value = "All" Then     Target.Offset(0, 1) = "All"     Target.Offset(0, 2) = "All" ElseIf Target.Cells.Address = "$E$8" And Target.Value = "All" Then     Target.Offset(0, 1) = "All" End If err: Exit Sub End Sub

May not work -      Range("a1").AutoFilter field:=1, Criteria1:=Date Will Work      Range("a1").AutoFilter field:=1, Criteria1:=Format(Date, "DD-MM-YYYY")