Solved

Access VBA to Output table to Excel Workbook

Posted on 2010-11-23
13
615 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:Mswetsky
  • 7
  • 3
  • 3
13 Comments
 
LVL 7

Expert Comment

by:RemRemRem
ID: 34200383
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 34200412

The message is
Run-time error '2282': The format in which you are attempting to output the current object is not available.
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 500 total points
ID: 34200424
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
 
LVL 7

Expert Comment

by:RemRemRem
ID: 34200430
Swap the "Excel97-Excel2003Workbook(*.xls)"
for
acFormatXLS

The explicit versioning may be giving you the problem.

-Rachel
0
 
LVL 61

Expert Comment

by:mbizup
ID: 34200434
A little too slow on my part.

The error is because of the quotes -- RemRemRem caught that in the first comment
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 34200479
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

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

 is still giving the 2282
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 34200522
I dont know if it matters but the table name is a variable
0
 
LVL 7

Expert Comment

by:RemRemRem
ID: 34200542
Ok, next mod - try having the following string (inclusive of all the double-single-double quotes) instead of your FinalTB:

"'" & FinalTB & "'"



-Rachel
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 34200558
I am sorry. I don't understand your suggestion
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 34200570
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
 
LVL 1

Author Comment

by:Mswetsky
ID: 34200614
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
 
LVL 1

Author Closing Comment

by:Mswetsky
ID: 34200685
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now