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.

VBA code to set zoom level for all sheets in workbook!!!

Zoom level in excel is one of the important aspect when we share it with end users. Sometimes, it is mandatory to have specific zool level set to each sheet in workbook. As we know, we can adjust zoom of a single worksheet at a time. So, it become a monotonous job when there are hundreds or thousands of sheets include in a workbook. Someone will be tired to adjust zoom level of numerous sheet in the workbook. However, using excel macro we can set specific zoom level to all sheets in the workbook. it can be done within a minute as soon as macro executed.

VBA code to Rename multiple files in a folder with a excel macro !!!

Rename multiple files from a folder is one of the craziest job. if someone is dealing with hundreds or thousands of files in a folder. To rename individual file one by one will definitely make someone crazy. As it is a monotonous task, which can lead to commit many mistakes while rename filenames individually. Moreover, there is not a single function available in excel to perform these job easily.

VBA code to zip multiple files!!!

Zip multiple files is a routine activity for file processing task. We zip multiple files to share our data to end users. We know zip files are very useful to compress files and folder which can be easily shared via email. Most of the times we need to work with multiple zip file simultaneously. We often zip multiple files once our data is processed which is a manual task usually. However, to perform end to end project automation zipping task can be automated. let's see how can we use excel macro to zip multiple programmatically.

VBA code to unzip files from multiple zip files!!!

Unzip files is one of the regular task in our daily activity. Thus, most of us often prefer zip files to publish our data to end users. As, we know zip files are extensively use to compress files and folder which can be easily shared with business owners. Sometimes, we need to work with multiple zip file simultaneously and we need to unzip multiple files. Therefore, it takes lot of time to unzip multiple zip files for that instance. However, using excel macro this task can be automated. let's see how can we use excel macro to unzip multiple zip files on a single go.