EXCEL/VBA sql

Hello,
I use below code in a vba code,

I need to have the
@FromZoneID = Cell A1
@ToZoneID = Cell B1

Can you please help

Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from
(Select (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) AS [PlanName],
-- (Select ServiceTypeID  from PricePlans where PricePlans.Priceplanid = PricesZone.Priceplanid) as [ServiceTypeID],
(select Description From Zones where ZoneID = ZoneFromID) AS [FromZone],
(select Description From Zones where ZoneID = ZoneToID) AS [ToZone],Price
From priceszone where PricePlanID in (3637,2834,2874,2876,2878,2880)
AND (ZONEFROMID = @ZONEFROMID OR ZONETOID = @ZONETOID)
)M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt

Thank you
W.E.BAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

W.E.BAuthor Commented:
Here is my full code

Sub Local()
 
    Dim cnt As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim stSQL As String
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range
    Dim Dterng As Variant
    Dim FrmDate As Variant

   
    Const stADO As String = "Provider=SQLOLEDB.1; Uid=sa; Pwd=c11111;" & _
    "Initial Catalog=database;" & _
    "Data Source=Laptop"
 
    Set wbBook = ActiveWorkbook
    Set wsSheet = wbBook.Worksheets(1)
     
    With wsSheet
        Set rnStart = .Range("A2")
    End With

 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from
(Select (select Description from PricePlans where PricePlanID = PricesZone.PricePlanID) AS [PlanName],
(select Description From Zones where ZoneID = ZoneFromID) AS [FromZone],
(select Description From Zones where ZoneID = ZoneToID) AS [ToZone],Price
From priceszone where PricePlanID in (3637,2834,2874,2876,2878,2880)
AND (ZONEFROMID = @ZONEFROMID OR ZONETOID = @ZONETOID)
)M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

    Set cnt = New ADODB.Connection
     
     'Delete old data
    ActiveSheet.Range("A2:Z65000").ClearContents
   
    With cnt
        .CursorLocation = adUseClient
        .Open stADO
        .CommandTimeout = 0
        Set rst = .Execute(stSQL)
    End With
     
    With wsSheet
        Set rnStart = .Range("A2")
    End With
 
    rnStart.CopyFromRecordset rst
 
    rst.Close
    cnt.Close
 
    Set rst = Nothing
    Set cnt = Nothing
   
       MsgBox "Records Found"
       
End Sub
0
W.E.BAuthor Commented:
Any help is appreciated.
0
Patrick MatthewsCommented:
 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from " & _
"(Select (select Description from PricePlans " & _
"where PricePlanID = PricesZone.PricePlanID) AS [PlanName], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneFromID) AS [FromZone], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneToID) AS [ToZone],Price " & _
"From priceszone " & _
"where PricePlanID in (3637,2834,2874,2876,2878,2880) " & _
"AND (ZONEFROMID = " & wsSheet.[a1] & " OR ZONETOID = " & _
wsSheet.[b1] & ") " & _
")M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

W.E.BAuthor Commented:
Hello,
thank you matthewspatrick,

if A1,B1 were texts , do I still use the same?
example:
cell A1 = T01(XXX)
cell B1= M01(YYY)

Thanks again.
0
Patrick MatthewsCommented:
It would change:

 stSQL = " Select FromZone,ToZone,[1],[2],[3],[4],[5],[6] from " & _
"(Select (select Description from PricePlans " & _
"where PricePlanID = PricesZone.PricePlanID) AS [PlanName], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneFromID) AS [FromZone], " & _
"(select Description From Zones " & _
"where ZoneID = ZoneToID) AS [ToZone],Price " & _
"From priceszone " & _
"where PricePlanID in (3637,2834,2874,2876,2878,2880) " & _
"AND (ZONEFROMID = '" & wsSheet.[a1] & "' OR ZONETOID = '" & _
wsSheet.[b1] & "') " & _
")M pivot(SUM(Price) FOR PlanName IN ([1],[2],[3],[4],[5],[6]))pvt "

Open in new window


Note how I added single-quotes around the parameter values.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
W.E.BAuthor Commented:
perfect,
thanks again.
0
W.E.BAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.