?
Solved

Excel 2007-VBA sql

Posted on 2012-09-07
9
Medium Priority
?
510 Views
Last Modified: 2012-09-07
Hello,
I'm trying to get a big select statement inside a VBA code,
Can you please help, I'm having some hard time.
Not sure IF it's ok to use this kind of Select inside !!

Option Explicit

Dim sh As Worksheet
Dim cnn As adodb.Connection

Public Sub GetSQLData()
    Set sh = Sheets("Orders")
    Dim rs As New adodb.Recordset
    Dim sql As String
    Set cnn = New adodb.Connection
   
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=XXXXX!;Initial Catalog=CC;Data Source=sql-Laptop;"

    sql = "SELECT  Replace (Reference,  ',', '') AS[Reference],    Caller AS [Requester],    CONVERT(varchar,SubTotalAmount) as [Shipping Charges], "
case Tax1Name  when 'GST' then Tax1Amount else 0 end as GST , case Tax1Name  when 'HST_ON' then Tax1Amount  else 0    end as HST_ON , case Tax1Name  when 'HST_NS'then Tax1Amount else 0 end as HST_NS  , case Tax1Name when 'HST_BC' then Tax1Amount else 0 end as HST_BC , case Tax2Name when 'TVQ' then Tax2Amount else 0 end as TVQ,
CONVERT(varchar,TotalAmount) AS [Total], CONVERT(varchar, FO.OrderDate, 1)  AS [OrderDate], CONVERT(varchar,FO.OrderNo) AS [OrderNumber],   PickupCompanyName AS [Shipper Name],    PickupContact AS [PU Contact], Replace (PickupStreet,  ',', '') AS [Shipper Street],PickupCity AS [Shipper CITY],PickupProvince AS [Shipper Province],
PickupPostalCode AS [Shipper Postal Code],DeliveryCompanyName AS [Shipper Name],DeliveryContact AS [DEL Contact],Replace (PickupStreet,  ',', '') AS [Delivery Street], DeliveryCity AS [Delivery City],DeliveryProvince AS [Delivery Province],DeliveryPostalCode AS [Delivery Postal Code] From (select InvoiceNumber, orderno
From finalizedordersinvoices where invoicenumber = (select top 1 invoicenumber from finalizedordersinvoices i inner join finalizedorders o on o.orderno= i.orderno  where Accountnumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001)   order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno  = foi.orderno
WHERE AccountNumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001) "
    'Delete old data
    sh.Range("A2:N65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub
0
Comment
Question by:W.E.B
  • 5
  • 4
9 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 38377097
What error do you get?

Do you get the same error if you do a simpler select? Something like
sql="select orderno from finalizedorders"

if you run your sql string in a new query window in SQL Tools, do you get the right result?

Thomas
0
 

Author Comment

by:W.E.B
ID: 38377137
Hello,
I tried a simple Select top 10 * from FinalizedOrders where accountnumber in ..

I get the results, NO ERRORS.

If I run the String in SQL, i get the correct results, NO ERRORS.

thanks
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38377149
Looks like you have an excess double quote at the end of the second line

   sql = "SELECT  Replace (Reference,  ',', '') AS[Reference],    Caller AS [Requester],    CONVERT(varchar,SubTotalAmount) as [Shipping Charges], "

Can you insert a debug line after the definition of your select, go to the immediate window in the VBA Editor (Ctrl+G), and copy the result into a new sql query in sql? That's usually how I spot issues in the string.

debug line code:
debug.print sql

Open in new window


Thomas
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:W.E.B
ID: 38377647
Hi Thomas,
thanks for taking the time trying to help,

I tried to take the double quotes out, but , they keep popping up again.

when I try to run the debug, it says Compile error: syntax error.

case Tax1Name  when 'GST' then Tax1Amount else 0 end as GST , case Tax1Name  when 'HST_ON' then Tax1Amount  else 0    end as HST_ON , case Tax1Name  when 'HST_NS' then Tax1Amount else 0 end as HST_NS  , case Tax1Name when 'HST_BC' then Tax1Amount else 0 end as HST_BC , case Tax2Name when 'TVQ' then Tax2Amount else 0 end as TVQ,
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38377666
Try deleting the line feeds if you have any.
0
 
LVL 39

Accepted Solution

by:
nutsch earned 1000 total points
ID: 38377689
Try updating your code to this (you'll need to update the password I guess)

Option Explicit

Dim sh As Worksheet
Dim cnn As adodb.Connection

Public Sub GetSQLData()
    Set sh = Sheets("Orders")
    Dim rs As New adodb.Recordset
    Dim sql As String
    Set cnn = New adodb.Connection
   
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=XXXXX!;Initial Catalog=CC;Data Source=sql-Laptop;"

    sql = "SELECT  Replace (Reference,  ',', '') AS[Reference],    Caller AS [Requester],    CONVERT(varchar,SubTotalAmount) as [Shipping Charges], " & _
" case Tax1Name  when 'GST' then Tax1Amount else 0 end as GST , case Tax1Name  when 'HST_ON' then Tax1Amount  else 0    end as HST_ON , case Tax1Name  when 'HST_NS'then Tax1Amount else 0 end as HST_NS  , case Tax1Name when 'HST_BC' then Tax1Amount else 0 end as HST_BC , case Tax2Name when 'TVQ' then Tax2Amount else 0 end as TVQ," & _
" CONVERT(varchar,TotalAmount) AS [Total], CONVERT(varchar, FO.OrderDate, 1)  AS [OrderDate], CONVERT(varchar,FO.OrderNo) AS [OrderNumber],   PickupCompanyName AS [Shipper Name],    PickupContact AS [PU Contact], Replace (PickupStreet,  ',', '') AS [Shipper Street],PickupCity AS [Shipper CITY],PickupProvince AS [Shipper Province]," & _
" PickupPostalCode AS [Shipper Postal Code],DeliveryCompanyName AS [Shipper Name],DeliveryContact AS [DEL Contact],Replace (PickupStreet,  ',', '') AS [Delivery Street], DeliveryCity AS [Delivery City],DeliveryProvince AS [Delivery Province],DeliveryPostalCode AS [Delivery Postal Code] From (select InvoiceNumber, orderno" & _
" From finalizedordersinvoices where invoicenumber = (select top 1 invoicenumber from finalizedordersinvoices i inner join finalizedorders o on o.orderno= i.orderno  where Accountnumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001)   order by invoicenumber desc)) foi left join finalizedorders fo on fo.orderno  = foi.orderno" & _
" WHERE AccountNumber in (45,7428,7429,7432,7433,7865,9500,9600,9601,9602,9603,10012,10013,10015,30001) "
    
    'Delete old data
    sh.range("A2:N65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Open in new window

0
 

Author Comment

by:W.E.B
ID: 38377936
Perfect,
thank you.for your time and help.
0
 

Author Closing Comment

by:W.E.B
ID: 38377938
thank you very much
0
 
LVL 39

Expert Comment

by:nutsch
ID: 38377957
Glad to help,

Thomas
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

621 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