microsoft excel - Execute SQL Query with VBA and fill sheet with data

Very new to any form of code. Attempting to run a script in VBA that will pull data from an access database using SQL and put it into an excel spreadsheet. Code is similar to below. Runs clean but I don't know how to use the data it is pulling. Thanks in advance.

Sub Get_Data()
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL, strInput As String

strFile = "S:\Location.Database.accdb"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

strInput = InputBox("Input Desired Name")

strSQL = "SELECT NAME, Location WHERE NAME =""'strInput'"";"

cn.Execute strSQL

cn.Close
Set cn = Nothing

End Sub

Answer

You need to fill your recordset object (That's the rs variable you declared). I have changed the code below to do just that. You can then copy the results from the recordset directly into a range with RANGE.copyfromrecordset below I have added that as well where I dump the results into SHeet1.Range("A1"). You'll probably want to change that. EDITED TO ADD: Just realized you are using late binding for ADODB. I have adjusted the code so it will actually work.

Sub Get_Data()
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL, strInput As String

strFile = "S:\Location.Database.accdb"

strCon = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFile

Set cn = CreateObject("ADODB.Connection")
cn.Open strCon

strInput = InputBox("Input Desired Name")

strSQL = "SELECT NAME, Location WHERE NAME =""'strInput'"";"

'Added the following four lines
Set rs = CreateObject("ADODB.RECORDSET")    
rs.activeconnection = cn    
rs.open strSQL    
Sheet1.Range("A1").CopyFromRecordSet rs

'removed
'cn.Execute strSQL


rs.close
cn.Close
Set cn = Nothing

End Sub

You can think of the RecordSet (rs in this case) as a virtual table that holds the results of the SQL. You can interact with the RecordSet on a record by record (with rs.MoveFirst, MoveNext, MoveLast ) and field by field basis (by iterating through the rs.fields collection), or you can just dump the results in a range.