First, to get our terminology straight, you don't attach a button to a module
nor do you run a module.
Command buttons are controls on forms.
Modules contain VBA code, Functions and Subs. You run the functions and
subs in modules. There are 4 kinds of modules. A Form Module is a property
of a form and contains VBA code specific to the form it belongs to. It
contains all the events that can be fired in the form and it's controls and
may contain user defined procedures specific to the form that can be called
from event procedures or from other user defined procedures. A Report module
is like a form module, but specific to a report. Reports have a different
set of events than a form. A Standard module contains subs and functions
that can be used by any form, report, or, in the case of functions, can be
called from a query. Class modules are a special kind of module that has
properties and methods like an object. It is treated like an object.
So, you need code in the Click event of a command button on a form to export
data to excel. Each region will put on a separate worksheet in the same
workbook. Each worksheet should contain only those rows for the region.
Here is how you can do it.
In addition to the command button on the form, you need a text box. It will
be used to store the region so you can use the text box to filter the query
you will be exporting. You also need a query that will return the same
regions from the table or tables your export query uses, so it will return
exactly the same regions, but you only want to return the region code one
time.
So in the code, create a recordset based on a query that will return the
region codes, store the region code in the text box, export the query using
the range argument of the TransferSpreadsheet method to put it in the correct
sheet.
First, however, filter your export query based on the value of the text box.
In the Criteria row of the region field, you would use:
Forms!FormName!ControlName (Use the real name where your region is stored in
the text box.
Here is an example you can start with and change the names to your real names:
Private Sub cmdExport_Click()
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT DISTINCT Region FROM
RegionTable;")
With rst
Do While Not .EOF
Me.txtRegion = ![Region]
Docmd.TransferSpreadsheet acExport, , "ExportQueryName",
"Q:\Spreadsheets\SomeFile.xls", True, Me.txtRegion
.MoveNext
Loop
.Close
End With
Set rst = Nothing
End Sub
