Export to Excel making Multiple Tabs

[Guest]

I'm trying to export a query to an Excel workbook so that for each change in
the variable stLv2Lv3Key the results go to a different spreadsheet. There
was one excellent post which addressed this issue about a year ago, but I
can't adapt the code so it works.

Here's what I tried:
Public vLv2Lv3Key As String

Private Sub Command4_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim vWorkbook As String
vWorkbook = "I:\Test.xls"
Set db = CurrentDb
Set rst = db.OpenRecordset("tbl Lookup Org Code Centers")
Do Until rst.EOF
vLv2Lv3Key = rst.Fields("stKeyL2L3") 'This is the primary for this
table
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qryExportTest", vWorkbook, , vLv2Lv3Key
'stKeyL2L3 is the foreign for the main table in qryExportTest
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub
Public Function Selected_Center()
Selected_Center = vLv2Lv3Key
End Function

The query qryExportTest Is defined:
SELECT * FROM [_qryRpt Paid Status 003]
WHERE [_qryRpt Paid Status 003].stKeyL2L3)=Selected_Center();

Everytime I run the transfer function it tells me the function
Selected_Center is not defined. I really appreciate any help I can get with
this.

 

[George Nicholson]

1) Is Selected_Center in a general module (i.e., not a Form, Report or any
other variation of a Class/Object module)? It has to be or the query won't
"see" it.

2) You should explicitly specify what datatype the function is returning
since the default data type is Variant and I could easily envision the query
compiler not being particularly found of that. I'm not really sure if this
would make a difference, but why not eliminate it from the list of things
that might possibly be wrong:
Public Function Selected_Center() As String

HTH,