Solved

Access VBA to Output table to Excel Workbook

Posted on 2010-11-23
13
621 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
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 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
 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

809 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