SQL to Excel - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL to Excel

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 10:39:10

Hello Everyone,

I now have the following code in Excel vba:


    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"
    Set comm.ActiveConnection = conn
    stringSQL = "SELECT Branch FROM track apps WHERE Branch = ['400']"
    'strSQL = strSQL & "SELECT Branch"
    'strSQL = strSQL & "FROM track apps;"
    'comm.CommandText = strSQL
    comm.Parameters(0) = Branch
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

How do I take my SQL code:


USE CHEC
SELECT DISTINCT
	DAT01.[_@051] AS Branch,
	DAT01.[_@550] AS LoanType,
	DAT01.[_@040] AS Date,
	DAT01.[_@LOAN#] AS LoanNum
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] 
	ON DAT01.[_@040] = [_@040]
	INNER JOIN [SMT_BRANCHES]
	ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]
WHERE 
	DAT01.[_@040] Between '06/01/2006' And '06/30/2006'
	AND DAT01.[_@051] = '540'
	And DAT01.[_@LOAN#] Like '2%' 
	And DAT01.[_@550] = '3'
GROUP BY 
	DAT01.[_@051],
	DAT01.[_@550],
	DAT01.[_@TP],
	DAT01.[_@040],
	DAT01.[_@LOAN#]
ORDER BY 
	DAT01.[_@051]

And insert it above do I have to do a strSQL for each line or is there a better way.

TIA

Kurt

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-19 : 11:02:49

[code]StringSQL = "SELECT DISTINCT " & _
"DAT01.[_@051] AS Branch," & _
"...."[/code]

likewise.

Harsh Athalye
India.
"Nothing is Impossible"

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 11:37:10

Hello Harsh and Everyone,

Thanks I tried your suggestion however there is a problem:

Here is my code now:


Sub command_dyer3()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
    strSQL = USE CHEC & _
    SELECT DISTINCT & _
    DAT01.[_@051] AS Branch, & _
    DAT01.[_@550] AS LoanType, & _
    DAT01.[_@040] AS Date,& _
    DAT01.[_@LOAN#] AS LoanNum & _
    FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] & _
    ON DAT01.[_@040] = [_@040] & _
    INNER JOIN [SMT_BRANCHES] & _
    ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr] & _
WHERE & _
    DAT01.[_@040] Between '06/01/2006' And '06/30/2006' & _
    AND DAT01.[_@051] = '540' & _
    And DAT01.[_@LOAN#] Like '2%' & _
    And DAT01.[_@550] = '3' & _
Group BY & _
    DAT01.[_@051], & _
    DAT01.[_@550], & _
    DAT01.[_@TP], & _
    DAT01.[_@040], & _
    DAT01.[_@LOAN#] & _
ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH, & _
    DAT01.[_@051]
    'stringSQL = "SELECT Branch FROM track apps WHERE Branch = ['400']"
    'strSQL = strSQL & "SELECT Branch"
    'strSQL = strSQL & "FROM track apps;"
    'comm.CommandText = strSQL
    comm.Parameters(0) = Branch
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close

When I try to run this I get a syntax error on this part:


    comm.CommandText = _
    strSQL = USE CHEC & _
    SELECT DISTINCT & _
    DAT01.[_@051] AS Branch, & _
    DAT01.[_@550] AS LoanType, & _
    DAT01.[_@040] AS Date,& _
    DAT01.[_@LOAN#] AS LoanNum & _
    FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] & _
    ON DAT01.[_@040] = [_@040] & _
    INNER JOIN [SMT_BRANCHES] & _
    ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr] & _
WHERE & _
    DAT01.[_@040] Between '06/01/2006' And '06/30/2006' & _
    AND DAT01.[_@051] = '540' & _
    And DAT01.[_@LOAN#] Like '2%' & _
    And DAT01.[_@550] = '3' & _
Group BY & _
    DAT01.[_@051], & _
    DAT01.[_@550], & _
    DAT01.[_@TP], & _
    DAT01.[_@040], & _
    DAT01.[_@LOAN#] & _
ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH, & _
    DAT01.[_@051]

What am I doing wrong?

TIA

Kurt

dewacorp.alliances

452 Posts

Posted - 2006-10-19 : 11:49:07

The syntax aftrt strSQL = needs to be in ""
Then you reference comm.CommandText = strSQL

strSQL = "USE CHEC & _
SELECT DISTINCT & _
DAT01.[_@051] AS Branch, & _
DAT01.[_@550] AS LoanType, & _
DAT01.[_@040] AS Date,& _
DAT01.[_@LOAN#] AS LoanNum & _
FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW] & _
ON DAT01.[_@040] = [_@040] & _
INNER JOIN [SMT_BRANCHES] & _
ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr] & _
WHERE & _
DAT01.[_@040] Between '06/01/2006' And '06/30/2006' & _
AND DAT01.[_@051] = '540' & _
And DAT01.[_@LOAN#] Like '2%' & _
And DAT01.[_@550] = '3' & _
Group BY & _
DAT01.[_@051], & _
DAT01.[_@550], & _
DAT01.[_@TP], & _
DAT01.[_@040], & _
DAT01.[_@LOAN#] & _
ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH, & _
DAT01.[_@051]"
comm.CommandText = strSQL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-19 : 11:53:02

You can't do that, curt !!

comm.CommandText = _
    strSQL = USE CHEC

First of all, you are not enclosing sql part in ""
secondly you are directly assigning to commandtext also and strSQL also which is wrong. Firstly build string variable and then assign that variable to CommandText like this:

strSQL = "Use Chec " & _
         "...."
comm.CommandText = strSQL

Harsh Athalye
India.
"Nothing is Impossible"

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-19 : 15:55:46

Hello Harsh and Everyone,

Thanks for your help so far.

Here is the code now in Excel vba:


Sub command_dyer3()
    Dim conn As New Connection
    Dim rec As New Recordset
    Dim comm As New Command
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("command")
    conn.Open "Provider=microsoft.jet.oledb.4.0;" + _
        "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
    strSQL = "USE CHEC" & _
    "SELECT DISTINCT" & _
        "DAT01.[_@051] AS Branch," & _
        "DAT01.[_@550] AS LoanType," & _
        "DAT01.[_@040] AS Date," & _
        "DAT01.[_@LOAN#] AS LoanNum" & _
        "FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]" & _
        "ON DAT01.[_@040] = [_@040]" & _
        "INNER JOIN [SMT_BRANCHES]" & _
        "ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]" & _
    "WHERE" & _
        "DAT01.[_@040] Between '06/01/2006' And '06/30/2006'" & _
        "AND DAT01.[_@051] = '540' " & _
        "And DAT01.[_@LOAN#] Like '2%' " & _
        "And DAT01.[_@550] = '3' " & _
    "Group BY" & _
        "DAT01.[_@051]," & _
        "DAT01.[_@550]," & _
        "DAT01.[_@TP]," & _
        "DAT01.[_@040]," & _
        "DAT01.[_@LOAN#]" & _
    "ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH," & _
        "DAT01.[_@051]"
    'stringSQL = "SELECT Branch FROM track apps WHERE Branch = ['400']"
    'strSQL = strSQL & "SELECT Branch"
    'strSQL = strSQL & "FROM track apps;"
    comm.CommandText = strSQL
    'comm.Parameters(0) =
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I get the error message Command text not set for the command object.

On line:

rec.Open comm

Any and all help is appreciated

TIA

Kurt
Baby Yak

dewacorp.alliances

452 Posts

Posted - 2006-10-19 : 19:39:48

You can't just do:

comm.CommandText = _
strSQL = "USE CHEC" & _
"SELECT DISTINCT" & _

Do like what I said before:

strSQL = "USE CHEC" & _
"SELECT DISTINCT" & _
...
...

comm.CommandText = strSQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-20 : 09:32:52

http://sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources