docmd.transfertext to Excel error

[GumbyD]

I have written some code to export a query to excel. I am using the docmd.transfertext command. When it hits the line of code that executes the export the error "Run Time Error 3190. Too many Fields defined" comes up. The query I am trying to export has only 15 columns and I have repaired and compacted the database. When I export the query to excel from the datasheet view it goes without any problem. I dug around on the web and could not find any references to this specific issue. If you know something about it I would love to know it too. It is an Access 2000 database. Here is the line of code that is producing the error:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EmployeeInfo", "C:\App.xls", -1

 

[boblarson]

Change this:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EmployeeInfo", "C:\App.xls", -1

To This:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "EmployeeInfo", "C:\App.xls", True

 

[GumbyD]

Thanks for the reply. I tried the fix you suggested, but no luck I still get the same error. I am hoping that you have actually seen this problem before so that I can at least know that it is fixable. Do you (or anyone else out there) have any other ideas?

Thanks,

 

[NJudson]

Not sure if you did this already but what if you create a macro to do the import and then convert the macro to vba to get the code. That's what I've done in the past. HTH

 

[ddmcn]

Try using...

DoCmd.OutputTo acOutputQuery, "EmployeeInfo", acFormatXLS, "C:\App.xls",

 

[GumbyD]

I will try it out. It runs at night so I will not know if it works until the morning.
Thanks!