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