OutputTo - Macros

[ruggierm1]

I'm trying to create a macro which exports a query to Excel. Everything works fine until I fill out the Output file field. No matter what I type in for the path, it won't accept it. When I leave the field blank it will prompt me for a location (which is what it's supposed to do). I have even adjusted the trust center settings to make sure the location that I am saving it to is considered a trusted location. Any help would be appreciated. I need to automate this task, as I will have 20 plus queries that need to run every month. BTW, I have also installed Service Packs 2 and 3.

 

[LaurieHamlin]

I always use code, not macros, however I know in code, Access is fussy about () and ". Hope that helps.

 

[rekrabnai]

This may be worth a try. I created a button on my form (btnExport) and on the 'onClick' event I wrote the following

Private Sub btnExport_Click()
Dim x As Boolean

x = ExportQuery("qryAllArea", "C:\temp\output.xls")

End Sub

Public Function ExportQuery(qryName As String, ExportFName As String) As Boolean

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qryName, ExportFName, True

End Function

The 'onClick' routine passes through 2 parameters to a function (the query name and the filename)
Hope this helps

Sometimes if you want to put a nail in a piece of wood, you just gotta hit it with a hammer!!

 

[MacroScope]

I do a lot of macro writing and I've never had a problem with importing or exporting. Macros are clean and easy generally speaking.

Put quotation marks around the file path.