Export query results to excel with specific file name.

[Oscar32]

Hello, I have a form with a list of names. When a specific name is selected from the list, a button is clicked and a query is run with the specific name as the criteria/filter.

I've written code to export these same query results to an Excel sheet, and I want the Excel file name saved with the name selected in the form.

Below is the code that I've written that doesn't work. Can someone tell me how to fix the code?

Thanks,

Code:

Dim strFileName As String

strFileName = SelectedItems(Me.lstName)

DoCmd.OutputTo acOutputQuery, "qryFocal_Sheet", acFormatXLS, "C:\JRS\Work\Equity\FY2015\" & strFileName & ".xls"

 

[pr2-eugin]

Hello Oscar32, what is the return String of the function SelectedItems ? Can you explain in Englis words what "doesn't work" actually means?

 

[Oscar32]

Hi Paul, thanks for responding.

When I click the button to execute my code I get a Run-time error 2302 message telling me that Access cannot save the output data to the file I've selected.

In this example I've selected "Engineering - June" from my list. The return string is ""Engineering - June"".

Does this help?

 

[pr2-eugin]

If your return actually contains a ", it cannot save. Windows does not allow some special characters in file names one of them is a double Quotes. So you can either make the return only the Engineering - June or use a Replace function to replace the double quotes with vbNullString.

 

[Oscar32]

That's exactly what I'm trying to do: remove all the quotes. I only want the file name to be Engineering June. I can't figure out how to remove the quotes.

 

[pr2-eugin]

Try,

strFileName = Replace(SelectedItems(Me.lstName), Chr(34), vbNullString)

 

[Oscar32]

It worked! Thank you so much!! I really appreciate it.

Oscar

 

[pr2-eugin]

Not a problem. Good luck