Solved

Access VBA to Output table to Excel Workbook

Posted on 2010-11-23
13
617 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

26 Experts available now in Live!

Get 1:1 Help Now