export to excel and name the file

[jln via AccessMonster.com]

Ok what im trying to do is export 4 query's to one excel workbook.

Im using the
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9,
"qry_AA_AC030QP2_export",()

Im not sure how to input a generated name for the file name. II would like
the name to be 370_AA, with the 370 changing depending on the where clause of
the query or on the value in a text box.

My other option would be to export all of the records for each inv i.e. 370
at one time by getting the investor numbers from a table.

 

[Guest]

You can use variables in the TransferSpreadsheet. Since you are exporting
four queries, you will need to change the query name, the name of the
worksheet to export to, and the file name. I would use something like this,
but since I don't know how you will create the file name, I can't tell you
how, just that you put it in a variable. The file name will remain the same,
but the query name and the sheet name will change.

Dim varQueryName as Variant
Dim varRangeName as Variant
Dim strFileName as String
Dim lngLoop As Long

varQueryName = Array("qry_AA_AC030QP2_export", _
"qry_BB_AC030QP2_export", "qry_CC_AC030QP2_export", _
"qry_DD_AC030QP2_export")
varRangeName = Array("FirstSheet", "SheetTwo", "ShortSheet", "LastSheet")
strFileName = ???

For lngLoop = 0 to 3
DoCmd.TransferSpreadsheet acExport,acSpreadsheetTypeExcel9, _
varQueryName(lngLoop), strFileName, True, varRangeName(lngLoop)
Next lngLoop