Solved

Creating an xls in Access 2010 VBA not working REVISITED

Posted on 2014-12-01
10
454 Views
Last Modified: 2014-12-04
Hey Everyone,  

I recently posted the question below and thought I had it working.  I put the code recommended into the program but when I published it as an ade I got the format error again.  When I run the adp the program works and it creates the xls file, but as an ade it does not.  Is there something about ade's that prevent them from running this?

Thank you all again.  Here is the original question:

I have an Access 2010 program that at one point allows the user to create an excel export file from the data (well originally I had the program creating a csv file).  Someone is helping me update this program so it works cleaner and in conjunction with the cloud so they changed the code slightly.  Now I get an error box that says "The format in which you are attempting to output the current object is not available"

 Here is the original code that worked:

   DoCmd.TransferText acExportDelim, "", "Export Plan Selection and Deduction To Send Sorted", "\\HOST33\ShareFile\CLIENTS\3CTRPS\Export for " & UCase(txtClientID.Value) & " Date-" & Replace(CStr(Date), "/", "-") & " Time-" & Replace(CStr(Time), ":", "-") & ".csv", True, ""

 Here is the code that he created:

 DoCmd.OutputTo acOutputReport, stdocname, acFormatXLSX, strFilename

 (the variables produce the same name for the file as the first one does).

 I tried changing it to read acFormatXLS also and it still did not work.

 Any help is really appreciated!
0
Comment
Question by:alevin16
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 167 total points
Comment Utility
Are you using Access 2007

Prior to Service Pack 2, you could export a table or query in Excel format, but not a report. Both OutputTo and SendObject would fail with error 2282: The format in which you are attempting to output the current object is not available. Applying SP2 should address this.
0
 

Author Comment

by:alevin16
Comment Utility
Thank you I am going to give that a shot
0
 

Author Comment

by:alevin16
Comment Utility
Hey Michael74,

Are you talking about the Access SP2 Runtime?  When I did a search for SP2 that came up.  I tried loading it on the computer but it said it was already loaded.

Did I get the right one?

Thanks
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
No the MS Office 2007 Service Pack 2

But there is actually Service Pack 3 now so better off to use that
http://support.microsoft.com/kb/949585
0
 

Author Comment

by:alevin16
Comment Utility
Hello Michael,

I am actually using Access 2010.  WOuld this still work?

Thanks
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 23

Expert Comment

by:Michael74
Comment Utility
No. It seems that the bug was carried into 2010, there are number of people getting the same error but no definitive solutions I have seen. Some possible solutions

1/ Re-install Access
2/ Use acFormatXLS  - Which did not work for you!
3/ If you are using a db created in an earlier version of Access you could try recreating your db in 2010.

These are just guesses and will take some time to implement. May be time to "Request Assistance" and see if any other experts can jump in and help as I am at a loss
0
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 166 total points
Comment Utility
I am not quite sure, if it is a bug or a feature.
The excel export works fine for a query. The previous csv export would have also been a table or query as source. So why change to report. A report has several elements as page texts, group headers and footers, which are not excel friendly at all. So if it is just for viewing, you might export directly to pdf alternatively :   DoCmd.OutputTo acOutputReport, stdocname, acFormatPDF, strFilename
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
Your first code is exporting a csv file. (working)
Your second code is exporting a Report to an Excel file. (Not working)
...so I am a bit confused as to what your ultimate goal is here...

In any event exporting "reports" to excel can be problematic if your report is anything but "simple"
Will any report Export to Excel with similar code?
0
 

Author Comment

by:alevin16
Comment Utility
Hello Everyone,

I really appreciate all the suggestions and help.  To answer Jeffrey, it was a csv file but when I handed it over to my "helper" he changed it to an xls.  I actually have a call into him and will be asking him why (I agree with you that there was no reason to change it from a csv).

I will have to put in some test code to see if the problem is for all excel exports or just this one.

Hopefully I will know more today.

Thanks All!
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
Comment Utility
In defense of your "helper", there may have been a good reason for changing the output format.
The kicker is that changing it to a Report and exporting he report is not working for you...
;-)
Remember that Excel can easily be set to open CSV files (just like it opens XLS files)

...again, can you export a simple report successfully to an Excel file?

I am not sure abut the ADE aspect of your question here,  ...but you stated that you saved it as an ADE file.
You also stated that you are using Access 2010.
Have you tried saving the db file as a .accde file instead?

JeffCoachman
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

763 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