Import XML Data in Excel Using VBA

Here is the complete VBA code to import XML Data into the Excel Workbook in a completely dynamic approach. Look at the following video.

Following is the VBA Code

Sub Impor_XML_Data()
    
    'Code By Kamal Bharkahda
    Application.ScreenUpdating = False
    Dim TargetSheet As Worksheet
    Dim ChooseFIle As Variant
    Dim TargetSheetName As String
    Dim TargetCellAddress As String
    
    TargetSheetName = Application.InputBox("Write a Sheet Name where you want to place the XML Data ***Case Sensitive***", "Target Sheet Name")

    TargetCellAddress = Application.InputBox("Write a Cell Address from where XML Data Start Placing", "Target Cell Address")
    
    
    Set TargetSheet = ThisWorkbook.Sheets(TargetSheetName)
    TargetSheet.UsedRange.Clear
    
    ChooseFIle = Application.GetOpenFilename("XML File (*.xml), *.xml", , "Kamal Bharakhda", , False)
    
    If ChooseFIle = vbNullString Then Exit Sub
    
    ThisWorkbook.XmlImport Url:=ChooseFIle, ImportMap:=Nothing, Overwrite:=True, Destination:=TargetSheet.Range(TargetCellAddress)
    
    MsgBox "Import Done"
    
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
    
End Sub

Import CSV Data into the Worksheet!

Hello Friends! Merry Christmas to all of you and to your friends and family.

Let me share a Christmas present for you all.

Do you want to import CSV file into the sheet and calculate the sum of the column data into the last row?

Well here is the file! and video of the file is in the comment box. Let me know if you guys have any queries.

Thank you. and enjoy your beautiful festival season.

Regards,
Kamal.

Sub ImportCSVFile()

    'By Kamal Bharakhda @ 919328093207 [E] kamal.9328093207@gmail.com
    Application.ScreenUpdating = False
    Dim xFileName As Variant
    Dim TargetSheet As Worksheet
    Dim Rg As Range
    Dim xAddress As String
    Dim TargetRange As Range
    
    Set TargetSheet = Sheets("DATABASE")
    TargetSheet.UsedRange.Clear
    xFileName = Application.GetOpenFilename("CSV File (*.csv), *.csv", , "Kamal Bharakhda", , False)
    
    If xFileName = False Then Exit Sub
     On Error Resume Next
    Set Rg = TargetSheet.Range("A1")
    On Error GoTo 0
    If Rg Is Nothing Then Exit Sub
    xAddress = Rg.Address
    With ActiveSheet.QueryTables.Add("TEXT;" & xFileName, Range(xAddress))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 936
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    
    Set TargetRange = TargetSheet.Range("A:A")
    
    'Splitting the Delimited Strings
    TargetRange.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    
    With TargetSheet
        Dim TotalRow As Long
        TotalRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Cells(TotalRow + 1, 3).Value = "Sub Total"
        .Cells(TotalRow + 1, 4).Value = Application.WorksheetFunction.Sum(.Range("D2:D" & TotalRow))
        With .Rows(TotalRow + 1).Font
            .Bold = True
            .Size = 13
        End With
        .UsedRange.Columns.AutoFit
    End With
    
    MsgBox "Process Done", vbInformation, "Message from Kamal"
    
    Application.ScreenUpdating = True
End Sub

IST (Indian Standard Time) to EST (Eastern Standard Time) Zone in Excel VBA.

Use the following function to get IST Date Time Values of the cell value into EST Date-Time Value. Paste the following VBA code into Module and that's it!

'/-----------------
Function IST_to_EST(ByRef SelectRange As Range) As Date
    
    ESTValue = SelectRange.Value - (0.0416667 * 10.5)
    IST_to_EST = ESTValue

End Function
'/-----------------

See this video to get more idea on this. Please comment if you have any query.

Delete Row from the Table when you have multiple tables on the same sheet

My client has query was to delete a row from the table. It sounds easy. But when the same sheet has more than two tables starting from the same row (defiantly from the different columns) it's a bit tricky to delete only a row from the Specific table.

let's say in the same worksheet You have Table1 and Table2 and you want to delete a row from table 1 dynamically by just selecting a cell inside the table range that's how my client wants to remove a row.

I prepared the workbook to show the demo to delete a row of specific table range by selecting the cell inside of it.

Here is the Code :

Private Sub CommandButton1_Click()
    
    If ActiveCell.Value = vbNullString Then Exit Sub
    
    Const RestrictedRow As Byte = 1
    Const FirstColofTable1 As Long = 1
    Const LastColofTable1 As Long = 5
    Const FirstColofTable2 As Long = 7
    Const LastColofTable2 As Long = 11
    Const Table1LeftIndex As String = "A"
    Const Table1RightIndex As String = "E"
    Const Table2LeftIndex As String = "G"
    Const Table2RightIndex As String = "K"
    Dim ActiveCellRow As Long
    Dim ActiveColumn As Long
    
    ActiveCellRow = VBA.Val(ActiveCell.Row)
    ActiveColumn = VBA.Val(ActiveCell.Column)

    If ActiveCellRow = RestrictedRow Then Exit Sub 
        
    With Sheets("Sheet2")
        If FirstColofTable1 <= ActiveColumn And ActiveColumn <= LastColofTable1 Then
            Sheets("Sheet2").Range(Table1LeftIndex & ActiveCellRow & ":" & Table1RightIndex & ActiveCellRow).Delete shift:=xlUp
        ElseIf FirstColofTable2 <= ActiveColumn And ActiveColumn <= LastColofTable2 Then
            Sheets("Sheet2").Range(Table2LeftIndex & ActiveCellRow & ":" & Table2RightIndex & ActiveCellRow).Delete shift:=xlUp
        End If
    End With
    
    MsgBox "Deleted"
    
End Sub

If my input is 1,2 & 3 then result should be 1,2,3,12,13,23,123

Q. Need VBA code to get different combinations. Eg. if my input is 1 & 2 then the result should be 1,2,12.
If my input is 1, 2 & 3 then result should be 1,2,3,12,13,23,123

Input can go up to 20 digits also
Also, result 12 and 21 should treat as same
Similarly, 123 & 321 or 231 or 312 is the same.

here is the perfect dynamic solution. You can add more than 20 digits and it will still provide you with the perfect result.

Put the following code in the module and that's it. I was working on this since yesterday. :D

Using Spin Button, Hide & Show the column one by one in Excel VBA

Show and Hide Columns one by one by Spin button up to mentioned columns limits.

this is going to be the best small tool for excel users.

here is the video. and here is the code of Spin Button.

Private Const DefaltWidth As Integer = 9
Dim TargetSheetName As String
Dim TargetSheet As Worksheet
Dim FirstColNo As Integer
Dim LastColNo As Integer
Dim I As Integer

Private Sub SpinButton1_SpinDown()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Unhiding Columns One By One from Limit One to Last Column
    For I = LastColNo To FirstColNo Step -1
        If TargetSheet.Columns(I).ColumnWidth = 0 Then
            TargetSheet.Columns(I).ColumnWidth = DefaltWidth
            Exit For 'This is most important point if you want to show columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub

Private Sub SpinButton1_SpinUp()
    Application.ScreenUpdating = False
    'Assigning Values to the Variables
    TargetSheetName = "Sheet1"
    Set TargetSheet = Application.ThisWorkbook.Sheets(TargetSheetName)
    FirstColNo = TargetSheet.Range("A7").Value
    LastColNo = TargetSheet.Range("A9").Value
    If TargetSheet.Range("A7").Value = Empty Or TargetSheet.Range("A9").Value = Empty Then
        MsgBox "Please Set The  Column Range Limits Before You Proceed", vbCritical, "Kamal Bharakhda"
        Exit Sub
    End If
    'Code For Hiding Columns One By One from Limit One to Last Column
    For I = FirstColNo To LastColNo Step 1
        If TargetSheet.Columns(I).ColumnWidth > 0 Then
            TargetSheet.Columns(I).ColumnWidth = 0
            Exit For 'This is most important point if you want to hide columns one by one.
        End If
    Next I
    Set TargetSheet = Nothing
    Application.ScreenUpdating = True
End Sub

Working With NAMED Ranges in VBA

1. The code to change the value of each cell of the name range at the same time

Sub Change_the_Value_of_Each_cell_in_Named_Range()
    Dim NameRange As Range
    Set NameRange = Range("SALES")
    Dim Eachcell As Range
    For Each Eachcell In NameRange
        Eachcell.Value = "Sales" & "2017"
    Next Eachcell
    MsgBox "Done"
End Sub

2. Let's Say You have a list of Named Range in Sheet3 where you want to change the value of each Named Range with the Same Format

Sub Change_The_Value_of_Each_Named_Range()
    Dim I As Long
    I = 2
    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetRange As Range
    Do Until Sheets(SheetName).Cells(I, 1).Value = vbNullString
        Set TargetRange = Range(Sheets(SheetName).Cells(I, 1).Value)
        TargetRange.Value = Sheets(SheetName).Cells(I, 1).Value & VBA.Format(Sheets(SheetName).Range("B6").Value, "YYYY")
    I = I + 1
    Loop
End Sub

3. TO RENAME THE WHOLE NAMED RANGES with EXACT FORMAT

Sub Rename_The_Name_Range()

    Dim SheetName As String
    SheetName = "Sheet3"
    Dim TargetSheet As Worksheet
    Set TargetSheet = Sheets(SheetName)
    Dim TargetNamedRange As Name
    Dim CurrentName As String
    Dim Iname As String
    Dim OriginalNamePlace As Integer
    Dim HelperString As String
    For Each TargetNamedRange In ThisWorkbook.Names
        Iname = TargetNamedRange.Name
        If VBA.InStr(1, Iname, "_") = 0 Then
            CurrentName = Iname & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        Else
            OriginalNamePlace = VBA.InStr(1, Iname, "_")
            HelperString = VBA.Left(Iname, OriginalNamePlace - 1)
            CurrentName = HelperString & VBA.Format(VBA.Date, "_yyyy")
            ThisWorkbook.Names(Iname).Name = CurrentName
        End If
    Next TargetNamedRange
    MsgBox "Done"
End Sub