Solved

EXCEL/VBA sql

Posted on 2013-01-30
9
215 Views
Last Modified: 2013-02-01
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
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
9 Comments
 

Author Comment

by:W.E.B
ID: 38836871
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
 

Author Comment

by:W.E.B
ID: 38837583
Any help is appreciated.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38842232
 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:W.E.B
ID: 38843686
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 38843714
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
 

Author Comment

by:W.E.B
ID: 38843725
perfect,
thanks again.
0
 

Author Closing Comment

by:W.E.B
ID: 38843728
Thank you
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel Date Question 8 46
count number 10 29
SQL Query 20 17
Error 1004 Excel 2013 11 13
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question