VBA Return Recordset to Excel 2007 Table

[UKSteveM]

Hello!

I'm trying to return a record set to a excel 2007 table but having no success, i'm back to basics now trying to return the recordset but cannot even manage that (code below). I know i am reading the record information and the debug.print provides all the correct information as i see in the query editor and what i would expect to be returned to the table/sheet. However when i run the code i get nothing returned to the sheet at all, no error is thrown from the code and the debug.print shows i have all the relevant information. I'd appreciate the forums direction as to what i am doing wrong here, and if anyone has any sage advice as to how to return the data directly to my table (updating the table) would be great.

-----------


Sub updatedata()

' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

GetData.Establish_Connection

With rsPubs
' Extract the required records.
.Open "SELECT tasklist_0.WBS, tasklist_0.Description, tasklist_0.TaskEnd, tasklist_0.Resource FROM mydb.tasklist tasklist_0 WHERE (tasklist_0.ProjectNumber='K60347') AND (tasklist_0.Date={d '2011-02-16'}) AND (tasklist_0.TaskEnd>={d '2011-02-16'} And tasklist_0.TaskEnd<={d '2011-03-16'}) ORDER BY tasklist_0.TaskEnd", conn

Debug.Print rsPubs.GetString

' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs


' Tidy up
.Close
End With

conn.Close
Set rsPubs = Nothing
Set conn = Nothing

End Sub

------------

 

[Richard Schollar]

Hi

Does it work if you comment out the GetString line?

 

[UKSteveM]

The debug line has no effect to the result, I added it to see if I was returning anything at all from the query!

The results in the immediate window are as follows;

4.2.1 Instruments 28/02/2011 Engineering
4.1.2 Purchase Order 03/03/2011
3.1.1.1 Vessel 08/03/2011 Engineering
3.1.1.2 Valves 11/03/2011 Engineering
3.1.1.3 Pipe 11/03/2011 Engineering
3.1.1.4 Manifold 11/03/2011 Engineering

but this is not returned to the sheet. I was wondering if it was due to returning multiple items to the single range, but changing the range size appears to have no effect.

In the concept, tables are populated from saved SQL queries, however in practice i want to change the query filters. I have the query string being generated in VBA and want to apply this to update the tables.

This last step of updating the tables from the VBA generated query seems to be unnecessarily difficult!

 

[Richard Schollar]

Did you actually try commenting out that line or did you just assume it wouldn't have any effect?

 

[UKSteveM]

I added the line out of frustration to begin with as i was not getting any result, adding the line made no change (except for the immediate window), removing it still didn't make any change.

No assumptions made. All tested.

 

[Richard Schollar]

Well there's nothing wrong with your code and assuming you aren't using something weird like Excel 97 it should return the results. You could try insreting the following code just before the CopyFromRecordset line:

rsPubs.MoveFirst

and leave the SetString line commented out for now.

 

[UKSteveM]

Thanks for the help Richard, i think it seems to be something in the SQL query, if i cahnge the query to "SELECT * FROM mydb.tasklist" i get a result. However its not the same result i get if i use the debug.print command. The debug command seems to give me what i would expect but the copyfromrecordset gives me something i was not expecting.

That appart, do you know how i can write this directly to an existing table? can i replace the target range with a table reference?

 

[Richard Schollar]

Yes - you should be able to use a QueryTable object which will be populated with the recordset and be held as a table in the sheet. Then you can just change the commandtext of the qt to change the results returned. Apologies but I do not have anything other than xl2002 where I am at the moment, but you should be able to find stuff on the above if you Google for QueryTable and 2007 (it's handled a little bit differently from earlier versions).

I don't understand why your recordset has got data in it (evidenced by the GetString) but isn't returning anything to your sheet. It makes no sense to me

 

[UKSteveM]

The querytable makes sense, its what i am trying to achieve, i have several tables on my spreadsheet at the moment which are all setup through the 'get external data>query wizard' however when i look in the worksheet in the VBA environment it says there are no query tables, the 'count' is zero for the sheet. Are they listed elsewhere on 2007? I'm having a hard time to find anything specific on Google and elsewhere which refers to 2007 specifically.

 

[Richard Schollar]

In 2007, the querytable is accessed thru the ListObject:

Sheets("Sheet1").ListObjects(1).QueryTables(1) '....

Confusing I know!

That bit in red may be just the QueryTable property of the ListObject (so no s(1) on the end - can't remember and can't test at present!).