Looping macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jayson - 20 Nov 2003 20:50 GMT

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

Reply to this Message

Ken Snell - 26 Nov 2003 15:38 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> For each occurance of a record in a particular table, I
> want to execute a macro.
[quoted text clipped - 26 lines]
>
> Thanks

Reply to this Message