Export to excel with variable filename

[goldenvision]

--------------------------------------------------------------------------------

Various posts on here seem to have flirted with the answer I am looking for but I can't seem to find anything specific. Any help would be appreciated.

I am looking to export a paramatised query to excel with a variable file name.

i.e. H:\Projects\CrosstabExport - [paramater value].xls

The threads dealing with adding a date to a file name seem to hint that this is possible but I'm not sure how to go about it.

Thanks in advance.

 

[bat17]

Something like :-

Dim strFile As String
strFile = "H:\Projects\CrosstabExport - " & [paramater value] & ".xls"
DoCmd.TransferSpreadsheet acExport, , MyQuery, strFile

HTH

Peter

 

[goldenvision]

I have applied your suggestion (see below) and receive the error message

Runtime Error 2465 - Cannot find the field "|" referred to in your expression

Private Sub btn_ExportCrosstabInfo_Click()
Dim strFile As String
strFile = "H:\Projects\CrosstabExport - " & [Payout_Crosstab]![Promotion] & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Payout_Crosstab", strFile, 0
If MsgBox("FILE EXPORTED SUCCESSFULLY. Do you wish to open Excel?", vbYesNo) = vbYes Then
DoCmd.RunMacro ("Open Payout In Excel")
Else
End If

End Sub[/b]

Any ideas?

 

[bat17]

I am assuming that the error is coming on the Transfer line.
When the error happens select debug then hover the mouse over the strFile variable and check that it is the value you were expecting.

Peter

 

[goldenvision]

The error is appearing on the strFile line when I hover the mouse over, it return strFile = ""

 

[bat17]

it looks like it does not like "[Payout_Crosstab]![Promotion]" then. Try:-
strFile = "H:\Projects\CrosstabExport - " & Forms![Payout_Crosstab]![Promotion] & ".xls"

I am assuming that it refers to a field on a form.

Also I noticed that you have set Field names to false, this makes no difference when you export, see below from Help

hasfieldnames
Use True (-1) to use the first row of the spreadsheet as field names when importing or linking. Use False (0) to treat the first row of the spreadsheet as normal data. If you leave this argument blank, the default (False) is assumed.
When you export Microsoft Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.

HTH

Peter

 

[goldenvision]

It is actually a query not a form. Is this solution still possible.

 

[bat17]

depends on what you are trying to do, What are you trying to extract from the query? is it its criteria or a calculated value?

if it is a value you may be able to use recordset function to get the data. if it is criteria that is hard coded you may be able to put the criteria on a form and link the query to that for the criteria.

Peter

 

[goldenvision]

It is a crosstab query that has been paramatised [concession]. It is this paramater than I am trying to select.

 

[bat17]

I have not used crosstab querys, the only suggestion I have is to try and pass your parameters to it from a form so that you can use the same source in your code.