pass list of values into queries and macros from table

[Serpno11]

I have 4-5 queries I'd like to run from a transfer spreadsheet macro both of which I'd like to have replaceable parts and have the macro repeat for a list of such values that are stored in a table. Is this possible?

My list is 175 values long and I don't want to create 700-900 queries. Thanks for any help you can offer!!

 

[crobg]

Yes, you can do this with QueryDefs. Here is an example with two functions. The first one creates your query to export the second one does the dirty work of using your values table. For simplicity sake I only do one query instead of 4 or 5. You can do all them in one function but it was easier to show you one.

Function CreateMyQuery(varValue As Variant, strQueryName As String)

Dim db As DAO.Database, qry As DAO.QueryDef, strSQL As String

strSQL = "Your SQL here" & varValue & ";"

Set db = CurrentDb()
Set qry = db.CreateQueryDef(strQueryName, strSQL)

db.Close

End Function

Function ExportMyQuery()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String

strSQL = "Select * from tblValues"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

Do Until rs.EOF = True
    CreateMyQuery rs![varValue], "Export"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Export", "c:\temp\export.xls"
    DoCmd.DeleteObject acQuery, "Export"
Loop

End Function