Looping macro

  • Thread starter Thread starter Jayson
  • Start date Start date
J

Jayson

For each occurance of a record in a particular table, I
want to execute a macro.

The macro executes a SQL query and using the OutputTo
method, outputs the results to an Excel file.

The SQL query takes an argument.

For the macro, the name of the Excel file needs to be
supplied.

I want to execute the macro and pass the argument for the
query and the Excel file name.

Does anyone know how to do this?

I have created a VBA module that I think will do this, but
I don't know how to execute it.

Option Compare Database
Option Explicit

Public Sub Create_Excel_Input_Tables(CSU)
For Each CSU In CSU_List_Table
DoCmd.OutputTo acOutputQuery, Input(CSU,
CSU_Inputs), acFormatXLS, CSU_Inputs, 0
Next
End Sub

Thanks
 
The VBA code that you've written is not an ACCESS macro....in ACCESS, macros
are different from VBA (not true for other Office applications).

Your code needs more steps. Something like this should get you started:

Private Sub SendEmails()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDB
Set rst = dbs.OpenRecordset("CSU_List_Table")
If rst.EOF = False Then
rst.MoveFirst
Do While rst.EOF = False
DoCmd.OutputTo acOutputQuery, rst!QueryName, acFormatXLS,
rst!ExcelFile
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub

The above code assumes that the table contains a query name and a path and
file name for the EXCEL file.

If this isn't what you're seeking, post back with more detailed information
and questions.
 
Back
Top