Creating an xls in Access 2010 VBA not working REVISITED

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!
alevin16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael FowlerSolutions ConsultantCommented:
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
alevin16Author Commented:
Thank you I am going to give that a shot
0
alevin16Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Michael FowlerSolutions ConsultantCommented:
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
alevin16Author Commented:
Hello Michael,

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

Thanks
0
Michael FowlerSolutions ConsultantCommented:
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
bonjour-autCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
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
alevin16Author Commented:
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
Jeffrey CoachmanMIS LiasonCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.