How do I refer to a specific cell value(date) in Excel by using VBA and SQL?

Question

I am trying to create a excel document where I can just write a date in a column in excel, and the date will update automatically in the data to the date in the column cell. Ex: if I change the date in a cell to 22/12/22 the data will change when running the query. I am quite new to VBA, so this might be a stupid question.. I am able extract the given dates when I write it manually in the query, however, I want to make it dynamic. This is the VBA query:

 Option Explicit
    
    Sub project()
    
    Dim cn As ADODB.connection Dim rs As ADODB.Recordset Dim SQL As String
    
    
    Dim server_name As String, database_name As String Let server_name = "xxx" Let database_name = "xxx"
    
        Set cn = New ADODB.connection
        Set rs = New ADODB.Recordset
        
        cn.ConnectionString = "Provider=SQLNCLI11;Server=" & server_name & _
            ";database=" & database_name & ";Integrated Security=SSPI;"
            
            
            SQL = "Select * FROM table_name where value_date = '" & ThisWorkbook.Sheets(1).Range("A1").Value & "' " & ""
    
            cn.Open
            rs.Open SQL, cn
            
            ThisWorkbook.Sheets(2).Range("A2").CopyFromRecordset rs
            
            cn.Close
            
            Set cn = Nothing
            
            
    
    End Sub

I have tried given the cell I am refering to a name, however it does not work.