Excel and VBA

[Kostas]

I am using this code to enter a recordset from SQL Server 7.0 into Excel.


set rs = cnn1.Execute(SQL)

'Worksheets.Add.Activate
ActiveSheet.Name = "Lucent_Hist"
[a2].CopyFromRecordset rs
ActiveSheet.Columns.AutoFit




The query runs, and the sheet gets renamed, but the data does not populate the spreadsheet.

What is wrong?

[richardj]

How come you're using the Execute method? I wouldn't expect that to return any records, except using ADO - but the .CopyFromRecordset method is intended for DAO (which would explain why it doesn't work...)

Try using the OpenRecordset method, rather than Execute, from DAO to set the recordset object - then the CopyFromRecordset method should work.

Another thing to check is whether your query is actually returning any records.

Tell us whether you're using ADO, and what version of Excel etc & things might be clearer.

Hope that helps.

[Kostas]

My bad...I am using ADO with Excel 2000

[richardj]

Okay, so you're using ADO.

The .CopyFromRecordset method, isn't intended for ADO. It should work if you use a DAO recordset instead, as follows (and make sure you're using an up-to-date version of DAO):
<vbcode>

Set db = DBEngine.OpenDatabase("C:\MyDatabase.mdb")

Set rs = db.OpenRecordset(...SQL...)

[a2].CopyFromRecordset rs

</vbcode>

[Kostas]

What code do I use if I want to stay with ADO?

CopyFromRecordset is supposed to work with ADO as well...

[coolbiz]

The only thing that I can think of is your rs does not contain any data. Try to check if your query actually returns anything:


set rs = cnn1.Execute(SQL)

If (rs.EOF) then
' error
MsgBox "No data returned!"
else
'Worksheets.Add.Activate
ActiveSheet.Name = "Lucent_Hist"
ActiveSheet.Range("A2").CopyFromRecordset rs ' use the Range object - it does not matter
ActiveSheet.Columns.AutoFit
End If




-Cool Bizs

[Kostas]

You were right, I am not getting any data returned. But when I run the same query in SQL Server 7.0, I get data for the same dates.

Here is my code...do you see where I'm going wrong? Could it be the way I'm passing my dates in? How should I declare them?


private Sub cmdQuery_Click()
Dim Start as date
Dim Finish as date
Dim SQL as string
Dim Cstring as string

Dim cmd as new Command
Dim rs as new Recordset
Dim cnn1 as new Connection ' connection object

on error GoTo errhandle

Start = txtDate1.Text
Finish = txtDate2.Text

SQL = "SELECT..." & _
"FROM ..." & _
"WHERE (date >= " & Start & " AND " & _
"date < " & Finish & ")"


' connect to your DB

Cstring = "Provider=sqloledb;Data Source=server;Initial Catalog=db;User Id=user;Password=pass;"
cnn1.Open Cstring

set cmd.ActiveConnection = cnn1 ' set the command object to point to your opened connection

With cmd
.Parameters.Append .CreateParameter("@start", adDBTimeStamp, adParamInput)
.Parameters.Append .CreateParameter("@finish", adDBTimeStamp, adParamInput)
.Parameters("@start").Value = Start
.Parameters("@finish").Value = Finish
End With


set rs = cnn1.Execute(SQL)

If (rs.EOF) then
MsgBox "No data returned!"
else
'Worksheets.Add.Activate
ActiveSheet.Name = "Lucent_Hist"
ActiveSheet.Range("A2").CopyFromRecordset rs
ActiveSheet.Columns.AutoFit
End If

[richardj]

You're right - that'll teach me to trust TechNet! Since CopyFromRecordset IS supposed to work with ADO, then, like coolbiz says, check the recordset actually returns records.

So, the code you were using should work fine, - just check .BOF / .EOF
Apologies again - I was looking at a TechNet article without double-checking the code in Excel2k.

[Kostas]

Not a problem...I thought the same thing until I checked MSDN. My query wasnt returning a thing...so now I have a new problem.

I posted my entire code, did you see it? Maybe you can tell me where I'm going wrong?

[richardj]

Normally, you need to include dates within single quote marks. So, where you have ...SQL = ....(date >= " & Start & " AND.... try inserting single quotes in the string, both before and after each date parameter (difficult to see in type but I'll write it anyway):

...SQL = ....(date >= '" & Start & "' AND....

Do the same with the Finish variable, & see what happens.

[coolbiz]

Date field is treated differently depending on the DB. For Access, Date field has to be surrounded by the # sign (ex: SQL = "SELECT * FROM DATETABLE WHERE START=#" & StartDate & "#"). For SQL Server, it might be different. As the replied from richardj, you might wanna try to enclose it with single-quotes (ex: SQL = "SELECT * FROM DATETABLE WHERE START='" & StartDate & "'"). I don't have SQL server installed, so i can't really check for you :)

Another thing to do is to use the CDate() function to convert the text value to date format. So your SQL statement should look like: SQL = "SELECT * FROM DATETABLE WHERE START='" & CDate(StartDate) & "'"). This function will throw an exception if your StartDate cannot be formatted to DATE type.

Just wondering why do you have ADO.Command object defined. This object is used to run STORED PROCEDURE but it looks that you're not doing so.

-Cool Bizs

[Kostas]

WE HAVE DATA RETRIEVAL!!!!

Thank you for the help...it was the single quotes I had to include.

I was using the .command object because originally I had a stored proc, but decided on another route. I left it in there just in case.

[Kostas]

THANK YOU VERY MUCH!!

It worked like a charm...now on to the next step.
I'll use this board again if I have more questions...seems like you have some good experience. I just graduated college so this is a great resource for me since I didnt have projects like this in school.