Importing SQL to Excel almost there

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

Importing SQL to Excel almost there

Author

Topic

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 08:38:33

Hello Everyone,

Here is my code 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]"
    comm.CommandText = strSQL
    'comm.Parameters(0) =
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I get the following error mesage:

Command text was not set for the command object on the following line:

rec.Open comm

What am I doing wrong here?

TIA

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 08:40:11

strSQL = "USE CHEC;" & _

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-23 : 08:43:16

or Prefix with table Name

CHEC.dbo.yourTable

Madhivanan

Failing to plan is Planning to fail

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 08:45:37

Hello Peso and everyone,

Thanks for your prompt reply again.

I am still getting the same error message on the same line after I fixed the code you suggested.

Any other toughts?

Thanks again!

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 08:53:44

Drop the line with comm.CommandText = _

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 08:57:03

Hello Peso,

Here is the code as I have it 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]"
    'comm.CommandText = strSQL
    'comm.Parameters(0) =
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

I get invalid SQL statement error message on the rec.Open comm line again.

Thanks again for your help.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 08:58:27

You commented out the wrong line...

Keep comm.CommandText = strSQL and comment out comm.CommandText = _

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 08:59:44

I''l write the code here, just in case

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]"
    comm.CommandText = strSQL
    'comm.Parameters(0) =
    rec.Open comm
    ws.[a1].CopyFromRecordset rec
    rec.Close: conn.Close
End Sub

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 09:02:58

Hello Peso,

I am stil getting the Command text was not set for the command object and I did comment out like you suggested. I have also checked my references in Tools, References in excel vba.

Are there any other references I might need to add?

Thanks,

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:10:13

Nope. You also need a lot of more spaces in the SQL text

    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]"

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:12:13

Also, in your code you don't need COMMAND object since you aren't creating parameters at all.
A simple Rec = conn.EXECUTE(strSQL) will do.

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 09:15:32

Hello Peso,

I copied and pasted your text. Still the same error message.

This is very frustrating.

Any other ideas??

Thanks again for your efforts.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:19:21

What is(are) the EXACT error message(s)?

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 09:24:16

Hello Peso,

It states Invalid SQL statement expected, 'INSERT', 'DELETE', 'PRODCEDURE' or 'UPDATE'

Again a big thanks to you.

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:29:44

Try to change following lines

 " ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH," & _
        "DAT01.[_@051]"

 " ORDER BY DAT01.[_@051]"

Otherwise, do a debug.print strsql and try to run that query in Query Analyzer.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:32:54

[code]Sub command_dyer3()
Dim conn As New Connection
Dim rec As Recordset

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("command")

strSQL = "Provider=microsoft.jet.oledb.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"

debug.print strSQL

conn.Open strSQL

strSQL = "SELECT DISTINCT " & _
"DAT01.[_@051] AS Branch," & _
"DAT01.[_@550] AS LoanType," & _
"DAT01.[_@040] AS Date," & _
"DAT01.[_@LOAN#] AS LoanNum" & _
" FROM CHEC..DAT01 INNER JOIN CHEC..[DATE_CONVERSION_TABLE_NEW]" & _
" ON DAT01.[_@040] = [_@040]" & _
" INNER JOIN CHEC..[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]"

rec = conn.execute(strsql)
ws.[a1].CopyFromRecordset rec
rec.Close
conn.Close

End Sub[/code]

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 09:34:37

Hello Peso,

It now stops here:

rec = conn.Execute(strSQL)

and states Invalid use of property.

TIA

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:41:59

This is the ordinary way to execute a command against the database

sql="SELECT companyname FROM Customers"
Set rs = conn.Execute(sql)

What differs from that with your code?

Did you debug.print strSQL and copy and paste the result into QA and run the query there?

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 09:51:51

Hello Peso,

I just realized I still had two statements that I was referencing from an Access datbase to figure this out.

I commented out:


    'strSQL = "Provider=microsoft.jet.oledb.4.0;" & _
    '    "Data Source=" + ThisWorkbook.Path + "\FromDyer.mdb;"

but still get the error:

Ivalid use of property on the

rec = conn.Execute(strSQL)

Thanks,

Kurt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-23 : 09:59:15

Why didn't you change .mdb file to the SQL database instead?

Peter Larsson
Helsingborg, Sweden

kdnichols
Posting Yak Master

232 Posts

Posted - 2006-10-23 : 10:02:55

Hello Peso,

I did for the Date Conversion table. The other files it references are all on the sql server references that I have.

That was originally my first thought. However the other problem is that the access database was using a cross tab query with a Transform command that is not avaialable in sql server. The code executes fine in sql server now it gives me the correct counts that I need. Pulling this into Excel is the only other part that I need now.

Hope this clarifies my process.

Thanks,

Kurt

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources