SQL Query from Dynamic Excel List

[Houstonking]

Hello I have attached query in power query connected to SQL Server Database - how do i get a list of account numbers in the query from excel list - the list is as below and they change daily -

1234444
4445555
6666555

SELECT Distinct CONVERT(VARCHAR(50), a.entered, 101) AS Entered
, CONVERT(VARCHAR(50), a.contractdate, 101) AS ContractDate
,a.[LDC]
, a.[BusinessUnit]
WHERE a.[AppLocation] IN ('Daily')
AND a.[AccountNo] in (here there needs to be a list of account numbers from excel sheet)

so should look like AND a.[AccountNo] in ('1234444','4445555','6666555')

 

[Trebor76]

Hi Houstonking,

Try this:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim strMyAccts As String
    Dim strMySQLStmt As String

    For Each rngMyCell In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)) 'Works from cell A2 down to the last row in Col. A of 'Sheet1'. Change to suit.
        strMyAccts = IIf(Len(strMyAccts) = 0, "'" & rngMyCell & "'", strMyAccts & ",'" & rngMyCell & "'")
    Next rngMyCell
    
    strMySQLStmt = "SELECT Distinct CONVERT(VARCHAR(50), a.entered, 101) AS Entered, CONVERT(VARCHAR(50), a.contractdate, 101) AS ContractDate, a.[LDC], a.[BusinessUnit] " & _
                   "WHERE a.[AppLocation] IN ('Daily') AND a.[AccountNo] in (" & strMyAccts & ")"
        
End Sub

Regards,

Robert

 

[Houstonking]

Hello Thanks for helping out - I am getting error on macro - the red part is expression error.

Sub Macro2()

Dim rngMyCell As Range
Dim strMyAccts As String
Dim strMySQLStmt As String

For Each rngMyCell In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).END(xlUp)) 'Works from cell A2 down to the last row in Col. A of 'Sheet1'. Change to suit.
strMyAccts = IIf(Len(strMyAccts) = 0, "'" & rngMyCell & "'", strMyAccts & ",'" & rngMyCell & "'")
Next rngMyCell

strMySQLStmt = " USE [ecowork] " & _

"SELECT Distinct CONVERT(VARCHAR(50), a.entered, 101) AS Entered " & _
",a.[MarketingMethodCd] " & _
"FROM [ecowork].[dbo].[Channel_Report_View] AS a WITH (nolock) " & _
"left join ecowork.dbo.newapps n with (nolock) on n.appid = a.appid " & _
"LEFT JOIN [ecoprod].[dbo].[TERRITORIES] AS b ON a.LDC = b.TERRCODE " & _
"left join eco.dbo.escoacct e on e.appid = a.appid " & _
"WHERE a.[AppLocation] IN ('Discovery','Buffer') " & _
"AND a.[BusinessUnit] in ('Daily') " & _
"AND a.[AccountNo] in (" & strMyAccts & ")"

End Sub

 

[Trebor76]

The following creates the strMySQLStmt string without issue...

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim strMyAccts As String
    Dim strMySQLStmt As String

    For Each rngMyCell In Sheets("Sheet1").Range("A2", Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)) 'Works from cell A2 down to the last row of 'Sheet1'. Change to suit.
        strMyAccts = IIf(Len(strMyAccts) = 0, "'" & rngMyCell & "'", strMyAccts & ",'" & rngMyCell & "'")
    Next rngMyCell
                   
    strMySQLStmt = "USE [ecowork] " & _
                   "SELECT Distinct CONVERT(VARCHAR(50), a.entered, 101) AS Entered ,a.[MarketingMethodCd] " & _
                   "FROM [ecowork].[dbo].[Channel_Report_View] AS a WITH (nolock) " & _
                   "LEFT JOIN ecowork.dbo.newapps n with (nolock) on n.appid = a.appid " & _
                   "LEFT JOIN [ecoprod].[dbo].[TERRITORIES] AS b ON a.LDC = b.TERRCODE " & _
                   "LEFT JOIN eco.dbo.escoacct e on e.appid = a.appid " & _
                   "WHERE a.[AppLocation] IN ('Discovery','Buffer') AND a.[BusinessUnit] in ('Daily') AND a.[AccountNo] IN (" & strMyAccts & ")"
        
End Sub

...so if it fails when passing the statement to the database the syntax of the statement itself must not be correct which I can't help with I'm afraid