command to run query

[slimjen1]

Hi all, I am using access 2003. I put a command button on a form so users can run a query and save and open in excel. When I click the button; the query sheet opens; the dialog opens to save the form but I get an error: "microsoft office access can't find the name "I" you entered in the expression" and the spreadsheet does not save or open. Heres my code:

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click

    Dim stDocName As String

    stDocName = "qryMyReport"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.OutputTo acOutputTable, "qryMyReport", acFormatXLS
Exit_Command9_Click:
    Exit Sub

Err_Command9_Click:
    MsgBox Err.Description
    Resume Exit_Command9_Click
    
End Sub

I don't want a report because the query has too many fields and the user prefers an excel spreadsheet.
Also, is there any way to change the fields headings once it outputs to excel. Just a thought for the end user.
I appreciate any help.
Thanks

 

[Alansidman]

I prefer to use the docmd.transferspreadsheet function to export queries to Excel.

http://msdn.microsoft.com/en-us/library/bb214134(v=office.12).aspx

http://msdn.microsoft.com/en-us/library/aa141534(v=office.10).aspx

Alan

 

[slimjen1]

ok Alan. This is my code now:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "qryMyReport", "S:\REPORT\ExportTest.xls"

It works but I will have multiple users clicking this command button. Is there a way to give them an option to export it to where ever they prefer to save it?
Thanks

 

[Alansidman]

When I do this and there will be multiple users, I set the default to save to the users desktop. Look at this example of how I do it. This is designed for AC 2003 with operating system XP.

 

[slimjen1]

Alan, I think this is exactly what Im looking for but I can't get your reportmenu to work. The "txtfilepath box has "#Name?" in it and when I click the export button I get an error: there is an invalid use of .(dot) or ! operator or invalid parentheses. Do I need to change something to work on my desktop? I just want to see the results before I adopt the code.
Thanks

 

[Alansidman]

Did you look at the code behind the txtfilepath box. You may have to modify it for your operating system and version of Access. As I said earlier, it was designed for Office 2003 and XP operating system.

Open the form in design view. Right click to get to properties. Look at the default value for the textbox and change it as necessary. If you delete the default value, then you can type in your own path but each user will need to know that. Alternatively, you could change that to a combo box that is fed from a table with every users name and the path to their desk top (or other default location) and use that as a filtering mechanism. That would ensure all exports go where you specifically want by user. Post back with any issues.

Alan

A

 

[slimjen1]

ok. I'll play around with it and post back after the weekend.
Thanks so much!