Access VBA to Output table to Excel Workbook

I am getting a message that the format which I am attempting to output the object is not available
I don't understand why.
Can someone help?
DoCmd.OutputTo acTable, FinalTb, "Excel97-Excel2003Workbook(*.xls)", "Q:\MIS\FASM\Wl2011\Reports\" & FinalTb & ".xls", False, "", 0

Open in new window

LVL 1
Mitch SwetskyBusiness AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
mbizupConnect With a Mentor Commented:
Your syntax would be like this (table name in quotes, acformatXLS out of quotes):

DoCmd.OutputTo acTable, "FinalTb", acFormatXLS, "Q:\MIS\FASM\Wlea2011\Reports\" & FinalTb & ".xls", False, , 0

That article mentions this as a known issue which has been fixed in Service Pack 2.

What version/Sevice pack are you running? And are you able to upgrade to SP2 if you aren't running it already?

Just a note, the syntax you posted originally, with the addition of quotes around the table name works (tested) for me in Access 2007:

DoCmd.OutputTo acTable, "FinalTb", "Excel97-Excel2003Workbook(*.xls)", "Q:\MIS\FASM\Wl2011\Reports\" & FinalTb & ".xls", False, "", 0
 
It sounds like you might need to update to SP2

0
 
RemRemRemCommented:
Try this instead:
DoCmd.OutputTo(acOutputTable, FinalTb, "Excel97-Excel2003Workbook(*.xls)", "Q:\MIS\FASM\Wl2011\Reports\" & FinalTb & ".xls", False, , 0)

Open in new window


Don't use "" in your TemplateFile value, and wrap parens around the attributes list. Also, if "FinalTB" isn't a variable you're sending in, it needs to be in quotes in both uses. I'm assuming it is, even though you haven't given us the precedent code.

Hope that helps.

-Rachel Morris
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:

The message is
Run-time error '2282': The format in which you are attempting to output the current object is not available.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
mbizupConnect With a Mentor Commented:
You need to enclose your table name in quotes:

DoCmd.OutputTo acTable, "FinalTb", "Excel97-Excel2003Workbook(*.xls)", "Q:\MIS\FASM\Wl2011\Reports\" & FinalTb & ".xls", False, "", 0

Open in new window


0
 
RemRemRemCommented:
Swap the "Excel97-Excel2003Workbook(*.xls)"
for
acFormatXLS

The explicit versioning may be giving you the problem.

-Rachel
0
 
mbizupCommented:
A little too slow on my part.

The error is because of the quotes -- RemRemRem caught that in the first comment
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I get a compile error
Syntax error.
There is a microsoft article referencing the above 2282 error but I don't understand the workaround
http://support.microsoft.com/kb/934833
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
DoCmd.OutputTo acTable, FinalTb, "acFormatXLS", "Q:\MIS\FASM\Wlea2011\Reports\" & FinalTb & ".xls", False, , 0

 is still giving the 2282
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I dont know if it matters but the table name is a variable
0
 
RemRemRemCommented:
Ok, next mod - try having the following string (inclusive of all the double-single-double quotes) instead of your FinalTB:

"'" & FinalTB & "'"



-Rachel
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I am sorry. I don't understand your suggestion
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
I am running 2003 SP3 on our server.
I did some development on my local 2007 SP2 but it needs to run on 2003
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
OK It looks like the below works in Access 2003.
Thank you both so much for your patience and assistance.

DoCmd.OutputTo acTable, FinalTb, acFormatXLS, "Q:\MIS\FASM\Wlea2011\Reports\" & FinalTb & ".xls", False, , 0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.