How Easily Use SQL Statement In Excel

VBA Code

Sub create_sheets()
    
    Sheets.Add.Name = "gender"
    Sheets.Add.Name = "country"
     
    get_data ("gender")
    get_data ("country")
End Sub
Sub get_data(sheet_name As String)

   

    '--- Declare Variables to store the connection, the result and the SQL query
    Dim connection As Object, result As Object, sql As String, recordCount As Long
    
    '--- Connect to the current datasource of the Excel file
    Set connection = CreateObject("ADODB.Connection")
    With connection
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    
    
    Dim d1, r1 As Long
   
   'get the last row with data
    r1 = Worksheets(sheet_name).Cells(Rows.Count, 1).End(xlUp).Row
    
    'clear old content
    Worksheets("country").Range("A2:h" & r1).ClearContents
    'Worksheets("country").Range("A2:h").ClearContents
    
   
     'get the last row with data
    d1 = Worksheets("data").Cells(Rows.Count, 1).End(xlUp).Row
    
    '--- Write the SQL Query
    If sheet_name = "country" Then
        
        sql = "SELECT * FROM [data$a1:h" & d1 & "] where country_code='US' "
        
    ElseIf sheet_name = "gender" Then
        
        sql = "SELECT * FROM [data$a1:h" & d1 & "] where gender='Male' "
        
    End If
    
    
    '--- Run the SQL query
    Set result = connection.Execute(sql)
    
    
   
    'copy header
    Sheets("data").Range("1:1").Copy Sheets(sheet_name).Range("1:1")
    
    
   recordCount = 1
    
    '--- Fetch information
    Do
        
        Dim i As Integer

        For i = 0 To 7
             
            Worksheets(sheet_name).Cells(recordCount + 1, i + 1).Value = result(i)
        Next i
        
        'insert data > id
        'Worksheets("country").Cells(recordCount + 1, 1).Value = result(0)
          
       
        result.MoveNext
        recordCount = recordCount + 1
       
    Loop Until result.EOF
    
    
End Sub