Hi, one routine of my job is to use sql to get the performance data for specific agents. I am writing a vba macro to make this process easier for me. So I want to open an Excel workbook, and entre the required agent ID / agent Pein in cell J9, and once I run the macro, I could get the data I need without openning Teradata and using almost the same query code everytime (only thing changing is the agent ID/Pein). Below is the Macro I've written, but it's not working. Can someone help me please? Thanks.
Dim AgentPein As String
AgentPein = Cells(9, "J")
Dim cnDB As New ADODB.Connection
Dim RS As New ADODB.Recordset, sqlText As String
Dim cmd As New ADODB.Command
sqlText = "Select * FROM SBI_MDU.VMENSA_SALES_COHORT_DLY where pein = 'AgentPein' "
cmd.ActiveConnection = cnDB
cmd.CommandType = adCmdText
cmd.CommandText = sqlText
RS.Open sqlText, cnDB
Set RS = cmd.Execute
For x = 0 To RS.Fields.Count - 1
Cells(1, x + 1) = RS.Fields(x).Name
Set RS = Nothing
Set cnDB = Nothing
sqlText = "Select * FROM SBI_MDU.VMENSA_SALES_COHORT_DLY where pein = '"&AgentPein"'"