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
Solved

Creating an xls in Access 2010 VBA not working REVISITED

Posted on 2014-12-01
10
471 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
ID: 40475017
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
ID: 40475024
Thank you I am going to give that a shot
0
 

Author Comment

by:alevin16
ID: 40475084
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 23

Expert Comment

by:Michael74
ID: 40475087
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
ID: 40476689
Hello Michael,

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

Thanks
0
 
LVL 23

Expert Comment

by:Michael74
ID: 40477279
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
ID: 40478480
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
ID: 40478561
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
ID: 40478831
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
ID: 40479373
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

840 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