?
Solved

Access VBA to Output table to Excel Workbook

Posted on 2010-11-23
13
Medium Priority
?
629 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 2000 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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