Output MS Access Query to Excel with vba

Question

Working with MS Access 2007, I have a query I'd like to run and export the results to a specific workbook in a saved Excel workbook. I have the following code written using DoCmd. First I open the query (this works) and then I try to output the results to excel.

DoCmd.OpenQuery "MyQueryName", acViewNormal, acEdit

DoCmd.OutputTo acOutputQuery, "Aging By Desk - Onboarding Team", acFormatXLS, _
    "filepath.SuperTest.xls", "SuperTest.xls", True

However, when this code is run, I get the following error message: "An Expression you entered is the wrong data type for one of the arguments". I've been playing around with each argument, but can't seem to locate the problem. Any ideas? Am I on the right path?

Primp

Hi Placet, when you get the error message and click on 'debug', which line is highlighted in yellow? – Defenestration

Answer 1

You've got too many arguments. From Microsoft's website:

expression.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)

Take out one of those Excel filenames you have and it should work.

Kirkuk

You both were correct, I had an extra argument. Now the macro is executed without an error. Even with the correct filepath (and i've double checked) "filepath.SuperTest.xls" the query information does not appear in the workbook SuperTest.xls when I open it. Any ideas? – Primp

filepath.SuperText.xls doesn't look like any valid path I've ever seen. Are you sure you don't mean "" & filepath & "\SuperText.xls"? – Kirkuk