VBA Coding error when transferred from Access 2003 to 2010 (along with Windows 7 upgr

[jmk]

Hi, I have been working on fixing a database that I did not design. Yesterday, I posted an issue and thankfully that one has been resolved. We were able to find the original database and access the VBA code from there. For reference the prior post is located at:



https://www.accessforums.net/program...tml#post192764

Now that I have access to the VBA code the issue is debugging code that is not working properly. While attempting a to run a function of the database I received a Microsoft Visual Basic error:

Run-time error '2282':

The format in which you are attempting to output the current object is not available.

This error has 3 options; continue, debug or help. When I attempt to debug this is the line of code that is not working:

DoCmd.OutputTo acOutputTable, "tblMacroTemplate", acSpreadsheetTypeExcel9, "G:\DEPARTMENT\11224458\Shared\Jobs\SET-UP\Macro Templates\" & FileName & ".xls", True

It appears that the database is not correctly connecting to excel for the export list function.

Any ideas are greatly appreaciated. Thanks!

[ssanfu]

It looks to me that you have mixed up arguments.

"acSpreadsheetTypeExcel9" is a spreadsheettype argument for the command "DoCmd.TransferSpreadsheet"

For the command "DoCmd.OutputTo" you should try "acFormatXLS"

So the line should be
DoCmd.OutputTo acOutputTable, "tblMacroTemplate", acFormatXLS, "G:\DEPARTMENT\11224458\Shared\Jobs\SET-UP\Macro Templates\" & FileName & ".xls", True

Disclaimer: I am using Access 2000, might be different in A2010.....

[jmk]

Found a fix!

acSpreadsheetTypeExcel9 is reffering to Excel 2003. Changing it to acSpreadsheetTypeExcel14 allows for the database to export to excel 2010. However, now a box pops up asking what output format. Is there anyway I can bypass this?

[jmk]

Sorry, I took a while on my post and didn't see your reply. I replaced with the acFormatXLS, as you suggested, and it bypassed the output format. Works perfectly now! Thank you!

[ssanfu]

Deleted... Was typing when you posted. Glad you fixed it.