Linking a field to a Query in VBA

[Dan_S]

Dear all,

I'm trying to use VBA to output the results of a list of Queries to Excel
spreadsheets using the TransferSpreadsheet command.

What I have is a source table with the Query names, and a relevant
department code, so I'd like to use this table to create only the queries
that match a specific department.

In short, I'm creating an Excel Workbook with a number of audits for each
department.

I've tried using multiple ways of passing a value to QueryDefs, but none of
the results work - they always result in errors. To be honest, I'm unsure as
to how the QueryDefs function works, and how to deal with it.

Thanks in advance for your help!

 

[Allen Browne]

Here's the basic idea:

Dim db As DAO.Database
Dim strSql As String
Dim strTable As String
set db = CurrentDb()
strTable = "Table1"
strSql = "SELECT " & strTable & ".* FROM " & strTable & _
" WHERE [DeptID] = 99;"
Debug.Print strSql
db.QueryDefs("qry4Export").SQL = strSql
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel7, _
"qry4Export", "C:\MyFolder\MyFile.xls"

This example assumes you want to export all fields from a table named
Table1, but only the records where DeptID is 99.

You can mock up a query that does what you want in design view, then switch
to SQL View to see an example of the statement you need to produce.

If your code fails, press Ctrl+G to open the Immediate Window, and see what
the Debug.Print line generated. Compare that to the SQL statement in the
query you mocked up.

Once you get this working for one table, you will need to OpenRecordset() to
loop through the records in your table of table names. Here's a basic
example of looping through the records in a recordset:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample