Output MS Access Query to Excel with vba

ms-access ms-access-2007 vba
RestitutorOrbis picture RestitutorOrbis · Aug 16, 2013 · Viewed 41.8k times · Source

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?

Answer

Johnny Bones picture Johnny Bones · Aug 16, 2013

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.

Related questions

Now() function with time trim

So the function =Now()....is there a way I can use this and only get the date, not the time? or is there just a function for this idea?

Read More
Import an Excel worksheet into Access using VBA

I am attempting to import an Excel spreadsheet into Access using some simple VBA code. The issue I have run into is there are 2 worksheets in the Excel file, and I need the 2nd worksheet to be imported. Is it …

Read More
Retrieve column values of the selected row of a multicolumn Access listbox

How can I retrieve the value of specified column of the selected row in a multicolumn listbox? I populate the listbox by setting the RowSource property with a SQL string. BoundColumn set to value 1. I can retrieve the value of …

Read More