Exporting to Excel with Automation using QueryDef

[Niezels]

First of all thanks to all posters on this forum. Though this is my first post I've already used quite a bit of answers/ example code here to solve issues in the database I'm working on.

But on this one I could not find the answer. As such my question here:

Background:
I'm hired within a project which has the goal of migrating 45000 computers to a new platform: Windows 7 with Office 2007.
For this project I manage the informationdatabase containing details on all in scope components (Computers, Useraccounts, Applications, Authorizations, etc)

The migration will be planned by clusters. A clusters is a subset of the scope based on either location, department or mixture of both. Such a cluster is assigned to a clustermanager who will be responsible for a succesfull migration.

Each clustermanager now has an information need on it's cluster. As such Users are now assigned to a clusterID.
Using query's a total of 7 views can been created. (Each query has a variable parsed containing the ClusterID).



The Clustermanagers like to have these views in a Excelsheet. with every view on a different sheet. For this a template has been created.

Of course I want this sheet to be created with a single click .

My challange
As I need to control how the data is exported I want to use automation to export the data.

However this method requeries the use of a recordset. Of which a parameter needs to be either the name of a Query or Table:

Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)

In a simple query where no variable is used There is no issue for this.
However I want to have the ClusterID (Which is a variable in all Queries) to be passed into the query.

As such I tried to contentate the query using VBA to a string called strSQL. I then want this string to be captured by a Querydef called qdf. So I would have a query I can call by name right.

I thought I could then parse this Querdef into the Recordset as a Query.

Is this in anyway possible? And if so how would the syntax be for the Recordset. My Querydef is:

Set qdf = dbs.CreateQueryDef("Query", strSQL)

My syntax for setting the record is:

Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbReadOnly)

If it is not possible to use a QueryDef in the recordset I would like to know alternatives to accoplisch what i want which is:

Exporting data to excel with control on where what is passed in the Excel sheet using Queries to fill the sheets where the querie has a variable in it.

Thanks a lot in advance for your reply.

[pbaldy]

There are ways around the problem of having a parameter in the query. Here's one:

Too Few Parameters. Expected 1 Error Message

Another is to wrap the form reference in the query in the Eval() function. As to your second idea, if you're going to go the route of building the SQL in VBA, I'd probably skip the QueryDef and use the SQL directly:

strSQL = "SELECT ..."
Set rst = dbs.OpenRecordset(strSQL , dbOpenDynaset, dbReadOnly)

[Niezels]

pbaldy,

Thanks for your reply. Though it was not what I was looking for it did hint me to the solution.

And for the record: You're reply was correct to my question I found out that my question was wrong .

Your reply made me understand that the error was not in the part of code where I thaught it was.

The error was simply that I was parsing in a value as a number where it is an text in the database design....

So that is now solved having me celebrate the victory of a completed script.

But rest assured I will probably return as I'm off course now looking to enhance the script.

[pbaldy]

Glad you got it sorted out, and welcome to the site by the way!