VBA code to list Sparklines in excel workbook by excel vba code!!!

Sparklines are graphical representation of data in single cell. A way to visualize data in a cell by cell manner. As we know, we can plot multiple sparklines in excel cells. Thus, there will be large number of sparklines exist in our excel reports. Sometime, it is very difficult to modify them. However, these sparklines details can be collected using VBA code. Thus, we can modify them using such a details. In this article, we will learn how to capture sparklines details using excel macro.

  
Here is the VBA code to capture Sparkline details in a workbook: 

Option Explicit
Public Const spl_sht As String = "Sparkline_Details"

Sub List_Sparklines()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com/

'Declare variable
Dim spa As SparklineGroups
Dim sprln As SparklineGroup
Dim sht As Worksheet
Dim i As Integer
Dim flag As Boolean

'Disable screen updating
Application.ScreenUpdating = False
Application.DisplayAlerts = False

flag = False

'Check if Sparkline_Details sheet exist in active workbook
For Each sht In ActiveWorkbook.Worksheets
    If sht.Name = spl_sht Then
        flag = True
    End If
Next sht

'Add Sparkline_Details sheet if it not exists
If flag = False Then
    ActiveWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = spl_sht
End If

ActiveWorkbook.Sheets(spl_sht).Activate
ActiveWorkbook.Sheets(spl_sht).Range("A1:Z500").ClearContents
ActiveWorkbook.Sheets(spl_sht).Range("A1").Value = "Sheet Name"
ActiveWorkbook.Sheets(spl_sht).Range("B1").Value = "Sparkline Location"
ActiveWorkbook.Sheets(spl_sht).Range("C1").Value = "Sparkline SourceData"
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select

i = 1

'Check each worksheet for Sparkline details
For Each sht In ActiveWorkbook.Worksheets

    Set spa = sht.UsedRange.SparklineGroups

    For Each sprln In spa
        'Pass sheet name value to A column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("A" & i + 1).Value = sht.Name
        
        'Pass Location range of Sparkline group to B column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("B" & i + 1).Value = sprln.Location.Address
        
        'Pass Source Data range of Sparkline group to C column in Sparkline Details tab
        ActiveWorkbook.Sheets(spl_sht).Range("C" & i + 1).Value = sprln.SourceData
        i = i + 1
    Next sprln

Next sht

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "All Sparkline listed successfully." & vbNewLine & vbNewLine & "Refer " & spl_sht & " sheet for Sparkline Details", vbInformation

End Sub

VBA code to retrieve outlook emails!!!

Outlook is popular email application to handle business emails. Thus, we use it for couple of hours to handle various email processing task in daily routine. Sometime, it is very time consuming to process certain emails received from specific sender or group. However, we can retrieve emails details to excel using a excel vba to code. Which can be very helpful to process such emails. Even, we can make a tracker while processing emails specifically processed for transaction kind of activities.

In this topic, we will see how to retrieve outlook emails detail from Inbox folder into excel workbook.

Before writing the macro add reference to outlook library as shown in below steps:

Step1:

Step2:

Now, we are ready to code below excel macro to retrieve outlook email details from Inbox folder.

Option Explicit

Sub Retrieve_Emails_From_Inbox()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Declare Variables
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim i As Long
Dim olDate As Date
Dim shtName As String
Dim lRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = False
    
'Pass Sheet name where you want email details
shtName = "Sheet1"

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")

'Pass date to variable for Received time
olDate = Date ' e.g. olDate = "10/01/2017"

ThisWorkbook.Sheets(shtName).Activate
Range("A1:D" & ActiveSheet.Rows.Count).Clear
Range("A1") = "Subject"
Range("B1") = "Received Time"
Range("C1") = "Sender Name"
Range("D1") = "Email Body"

Set olFolder = olNs.GetDefaultFolder(olFolderInbox)
    
'For loop to check email item for Received time(Date)
    For i = olFolder.Items.Count To 1 Step -1
    Application.StatusBar = "Processing email item (" & i & ")..."
        If TypeOf olFolder.Items(i) Is MailItem Then
            Set olMail = olFolder.Items(i)
            
            'if condition to check Received time
            If InStr(olMail.ReceivedTime, olDate) > 0 Then
            
                'Variable to get last row count in activesheet
                lRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
                
                'Pass values to column A, B, C and D in Activesheet
                Range("A" & lRow + 1) = olMail.Subject
                Range("B" & lRow + 1) = olMail.ReceivedTime
                Range("C" & lRow + 1) = olMail.SenderName
                Range("D" & lRow + 1) = olMail.Body
            End If
        End If
    
    Next i
    Set olFolder = Nothing
        
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = True
    
MsgBox "All email retrieved for received date: " & olDate, vbInformation
    
End Sub

VBA code to collect pivot data source details for all pivot in a workbook!!!

Data Source is key factor to create pivot table in excel. On the basis of data source pivot get refreshed and we can made changes in pivot structure. Thus, to know pivot data source is very essential to structure pivot layout. In this topic, we will see how can be data source information for various pivot collected using excel macro.

Here, we go for excel macro which will collect pivot data source details for all pivots in a workbook: 

Option Explicit

Sub Pivot_DataSource()
' Author: Dreams24d
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Declare Variables
Dim pvt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim sht As String
Dim pvtSource As String
Dim pvtName As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

strPvt = "       Pivot Name     Sheet Name   Source Data" & vbNewLine
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

        'Loop to select pivot tables in each worksheet
        For Each pvt In ws.PivotTables
           
           pvtName = pvt.Name
           pvtSource = pvt.SourceData
           sht = ws.Name
           
            'Pass Pivot details to variables in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".    " & pvtName & "         " & sht & "       " & pvtSource & vbNewLine
            
            'Uncomment below lines to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pvtName
            'ActiveWorkbook.Sheets("Sheet1").Range("B" & i).Value = sht

            'ActiveWorkbook.Sheets("Sheet1").Range("C" & i).Value = pvtSource

            i = i + 1
            
        Next pvt
    Next ws
    
MsgBox "Pivot Tables and their data source listed as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot and Data Source"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

VBA code to check filter status in worksheet!!!

Filter is used to check specific type of data in excel workbooks. As we know excel is capable of handling thousand of records with various kind of datatype. So, it obvious to have data in text, dates, percentage and number format. These format are mainly know as datatype. We often use filter to see particular type of data in excel worksheet. While processing data using macro it is very important to check whether data is filtered on not. 

Sometimes, our data has some filtering criteria on excel worksheet which is not needed while running the macro for specific automation task. Therefore, it is necessary to remove filter on the data before running the macro. In this topic, we will see how we can check filter status in excel worksheet.

  

Here is the VBA code to check filter status in worksheet: 

Option Explicit

Sub Check_Autofilter_Status()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim wbk as Workbook
Dim sht As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Variable used to assign worksheet name
sht="Sheet1"

'Variable set for activeworkbook
Set wbk = Activeworkbook 

If wbk.Sheets(sht).FilterMode = False Then

    'Put your code when filter is disabled
    MsgBox "Filter is not enabled", vbInformation
Else

    'Put your code when filter is enabled
    MsgBox "Filter is enabled", vbInformation
End If

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

VBA code to import data from text file!!!

Text files can be used to store data in certain scenario. However, we need to use data from these text files in our excel template in specific format. So, use of text files data into excel can be a manual and lead to data accuracy miss. Sometimes, there will be data mismatch in excel and text file. To avoid such a data inaccuracy a simple excel macro can be used while will help to import data from text file to excel workbook. Let's see how data can be import from text file to excel using excel macro.

  

Here is the VBA code to import data from text file to excel: 

Option Explicit

Sub Import_From_Text()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim TXT_FILE_PATH As String
Dim DESTINATION_RNG As String
Dim isTAB, isSEMICOLON, isCOMMA, isSPACE As Boolean
Dim Cn As Variant

'Variable used to assign data delimiter type in text file data
'Here Tab used for text file data delimiter
isTAB = True
isSEMICOLON = False
isCOMMA = False
isSPACE = False

'Variable used to provide text file path and range from activesheet
'to import data from text file
TXT_FILE_PATH = "C:\Users\Dreams\Desktop\Sample_Test_File.txt"
DESTINATION_RNG = "$A$1"

    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & TXT_FILE_PATH, Destination:=Range(DESTINATION_RNG))
        .Name = "Test_Connection"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = isTAB
        .TextFileSemicolonDelimiter = isSEMICOLON
        .TextFileCommaDelimiter = isCOMMA
        .TextFileSpaceDelimiter = isSPACE
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
'For loop to delete connection created during text data import
    For Each Cn In ActiveSheet.QueryTables
        Cn.Delete
    Next Cn
    
MsgBox "Text file data imported successfully.", vbInformation, "Text file data import"


End Sub

VBA code to change tab color in excel workbook!!!

Tabs are basic elements of each workbook in excel application. In excel, tabs can be colored with different colors. Using color coding to each tab, user can specify their use based on the data containing each tab. However, color can be applied in excel tab using excel macro. let's see how tabs can be colored using excel macro.

  

Here is the VBA code to change color for each tab in a workbook: 

Option Explicit

Sub Change_Tab_Color()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://www.vbatricksntips.com

'Declare Variable
Dim i As Integer
Dim sht As Worksheet
    
'For loop to change color of each tab
  For i = 1 To ActiveWorkbook.Sheets.Count
    Sheets(i).Select
    Set sht = ActiveSheet
    'Change color index values 21 to your required color. refer color index values from table given at end of this article
    sht.Tab.ColorIndex = 21
  Next i

'Activate Sheet1
Sheets(1).Select

MsgBox "Color changed for all tabs.", vbInformation, "Change Tab Color"
End Sub

Refer below table for color index value for few colors:

VBA code list of all tables in a workbook by excel macro!!!

Table is a root element of any data processing application. It is used to stored data in the form of row and column. In excel, it can be used as one of data visualization. So, table is used in a workbook frequently. As a result, various table will be created in a single workbook. However, To make a list of all tables will be a manual task in excel. It can be automate by using VBA code as follows.

  
Here is the VBA code to list all tables in a workbook: 


Option Explicit

Sub List_All_Table()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim objTable As ListObject
Dim ws As Worksheet
Dim strTbl As String
Dim i As Integer

strTbl = ""
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets

'Loop to select each table in a worksheet
        For Each objTable In ws.ListObjects
           
            'Pass table names to variable in order to show in message. comment or delete this if not required
            strTbl = strTbl & i & ".  " & objTable.Name & vbNewLine
            
            'Uncomment below line to get table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = objTable.Name
            i = i + 1
            
        Next objTable
    Next ws
    
MsgBox "List of Tables as Below: " & vbNewLine & vbNewLine & strTbl, vbInformation, "Table List"

End Sub

VBA code to list all hidden sheet in a workbook!!!

Excel allows user to hide worksheets which are not required or wanted it to be hidden. As a result, there will be numerous worksheets to be hidden in a single workbook. Sometime, we need to figure out which hidden sheets we want and which should be deleted from the workbook. In such scenario, list down all hidden sheets will be a quite manual task. Obviously, it can consume couple of hours if there are lot of workbooks contains hundred of worksheets in each workbook. So, this task can be automate with few line of excel macro.

  
Here is the VBA code to list all hidden sheet in the workbook: 

Option Explicit

Sub List_of_Hidden_Worksheet()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim strSht As String
Dim sht As Worksheet
Dim i As Integer

strSht = ""

i = 1

'For loop to check each worksheets for hidden property
    For Each sht In ActiveWorkbook.Worksheets
        
        If Not Worksheets(sht.Name).Visible Then
            
            'Pass names of hidden sheets into variable to show in message box. comment or delete this if not required
            strSht = strSht & i & ".  " & sht.Name & vbNewLine
            
            'Uncomment below line to get hidden Sheets list in Sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = sht.Name
            
            i = i + 1
        End If
    Next sht
    
MsgBox "List of hidden sheets as Below: " & vbNewLine & vbNewLine & strSht, vbInformation, "Hidden Worksheets"

End Sub

VBA code to list all pivot tables in a workbook!!!

Pivot table is one of the powerful data interpretation tool in excel. Which is used to summarise large amount of data at a glance. It is mostly used in various reporting task. Because of this pivots are extensively used in the excel reports and dashboards. Thus, there will be various pivots tables can be used in excel workbook. In order to validate pivot tables availability in a workbook someone needs list of all pivot tables. Obviously, to collect list of all pivot tables again result into a manual task which can be time consuming. But, this can be automate by a excel macro.

  
Here is the excel macro which will give us list of all pivot in a workbook: 

Option Explicit

Sub List_All_Pivots()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim pt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim i As Integer

strPvt = ""
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

'Loop to select each pivot table for a single worksheet
        For Each pt In ws.PivotTables
           
            'Pass Pivot names to variable in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".  " & pt.Name & vbNewLine
            
            'Uncomment below line to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pt.Name
            i = i + 1
            
        Next pt
    Next ws
    
MsgBox "List of Pivot Tables as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot List"

End Sub

VBA code to return version of excel application !!!

Excel version is one of the important factor while developing macros which executes on various systems. It is quite possible to have different office version on installed on different systems. It may through runtime errors in macro code when executed to a different excel version.  Thus, to prevent such kind of bugs we can determine excel version to add version specific error handler in VBA code. Using VBA code we can identify version of currently install excel application.