SQL Query from Dynamic Excel List

Houstonking

New Member
Joined
Jul 12, 2016
Messages
41
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')
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Hi Houstonking,

Try this:

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
The following creates the strMySQLStmt string without issue...

VBA Code:
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 :(
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top